Hi everyone,
I am currently working on a small project and I need an autoshape to automatically change color based on a cell value. I have an attached example and in this case it's cell B8. B8 sums up data from cell B3:B6. I've created the code, but the autoshape won't automatically change color. I need to double click on B8 and press enter, then the color changes.
Any ideas what might be wrong?
Here's my code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Macro purpose: Make autoshape change color based on value in cell B8
If Not Intersect(Target, Range("B8")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value < 10 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen
ElseIf Target.Value >= 20 And Target.Value < 30 Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbYellow
Else
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed
End If
End If
End If
End Sub
Thanks
Bookmarks