How to change shape color based on cell value in Excel?

Change the shape color based on a specific cell value may be an interesting task in Excel, for example, if the cell value in A1 is less than 100, the shape color is red, if A1 is greater than 100 and less than 200, the shape color is yellow, and when A1 is greater than 200, the shape color is green as following screenshot shown. To change the color of the shape based on a cell value, this article will introduce method for you.

Change shape color based on cell value with VBA code

Change shape color based on cell value with VBA code

The below VBA code can help you to change the shape color based on a cell value, please do as follows:

1. Right click the sheet tab which you want to change the shape color, and then select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window.

VBA code: Change shape color based on cell value:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160704
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) Then
        If Target.Value < 100 Then
            ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed
        ElseIf Target.Value >= 100 And Target.Value < 200 Then
            ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbYellow
        Else
            ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen
        End If
    End If
End Sub

2. And then when you enter the value in cell A1, the shape color will be changed with the cell value as you defined.

Note: In the above code, A1 is the cell value your shape color would be changed based on, and the Oval 1 is the shape name of your inserted shape, you can change them to your need.