I am trying to get a shape to appear and disappear based on the result of a formula, but I am having trouble making this dynamic. The code below works when I hard code the value into cell U2, but when I try to have this formula =IF(V2<=Dashboard!$Z$2,1,0) in the cell to make it more dynamic, it will not work. I have seen a few mentions of this by setting up Cases, but honestly am not that well versed to make it work. Any advise would be great.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("U2")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) Then
If Target.Value = 1 Then
With ActiveSheet.Shapes("Freeform 29")
.Fill.Transparency = 0.5
.Fill.ForeColor.RGB = RGB(20, 55, 90)
.Line.ForeColor.RGB = RGB(20, 55, 90)
.Line.Transparency = 0
End With
ElseIf Target.Value = 0 Then
With ActiveSheet.Shapes("Freeform 29")
.Fill.Transparency = 1
.Fill.ForeColor.RGB = RGB(20, 55, 90)
.Line.ForeColor.RGB = RGB(20, 55, 90)
.Line.Transparency = 1
End With
End If
End If
End Sub