VBA code for changing fillcolor of shape based on value in cell

Question

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E9")) Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) Then
        If Target.Value < 0 Then
            ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = vbRed
        Else
            ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = vbGreen
        End If
    End If
End Sub

In cell E9 I have the formula:

=IF(H9="";"";INDEX(Sales!E39:P39;MATCH(Main!AE3;Sales!E34:P34;0)))

This doesn't seem to work, only if I write manually for example 500 in E9. Any solutions?

Does your Index/Match formula return a value when you don't enter one manually? — ashleedawg, Nov 21 '17 at 13:04
Index/match formula return values when I change the month in a drop down list. — TheNarsisisst, Nov 21 '17 at 13:12

score 1 · Accepted Answer · answered Nov 21 '17 at 13:10

1

The Sheet Change Event doesn't get triggered when the cell content is changed by a formula. To achieve this you need Sheet Calculate Event rather.

Give this a try. Place the following code on Sheet Module.

Private Sub Worksheet_Calculate()
Dim cell As Range
Set cell = Range("E9")
If IsNumeric(cell) Then
    If cell.Value < 0 Then
        ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = vbRed
    Else
        ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = vbGreen
    End If
End If
End Sub

answered Nov 21 '17 at 13:10

Subodh Tiwari sktneer

9,906
2
18
22

Works like a charm. Thanks! – TheNarsisisst Nov 21 '17 at 13:14
@TheNarsisisst You're welcome! Glad it worked for you. Please take a minute to accept the answer as Solution to mark your question as Solved. :) – Subodh Tiwari sktneer Nov 21 '17 at 13:25
1

@TheNarsisisst you are free to accept the answer, if it helps you, this means a lot to people. – AntiDrondert Nov 21 '17 at 13:25
1

@sktneer beat me to it :D – AntiDrondert Nov 21 '17 at 13:26

VBA code for changing fillcolor of shape based on value in cell

1 Answers1