Hi Guys and Girls
Im really needing help here. Ive some code that changes my AutoShape button colour. It works fine when I enter a value into the trigger cell and press enter.
Ive had to adapt my spreadsheet slightly and the triggercell now has an IF Statement to give me either Yes No or Blank. Now the code wont work unless i manually click into the trigger cell and press enter.
Id like this to run automatically as the IF Statement changes.
Here is my code. (the triggercell is on a separate worksheet sheet from the Autoshape buttons.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myTriggerCell As Range
Set myTriggerCell = Sheets("WC").Range("C152")
If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then
Select Case myTriggerCell.Value
Case "Yes": Sheets("Dashboard").Shapes("Rounded Rectangle 2").Fill.ForeColor.RGB = RGB(51, 204, 51)
Case "No": Sheets("Dashboard").Shapes("Rounded Rectangle 2").Fill.ForeColor.RGB = RGB(247, 150, 70)
Case "": Sheets("Dashboard").Shapes("Rounded Rectangle 2").Fill.ForeColor.RGB = RGB(79, 129, 189)
For Each c In Sheets("WC").Range("F6:F39")
If c.Value >= DateTime.Date Then
Sheets("Dashboard").Shapes("Rounded Rectangle 2").Fill.ForeColor.RGB = RGB(255, 0, 0)
Call Change
End If
Next
End Select
End If
End Sub
Can anyone offer a solution.
Thanks
SB
Bookmarks