I have some VBA code to change the cell colours in a range of cells. Thing is it only works when those particular cells are changed. These cells have formulas in so change when other cells have data entered into them, hope that makes sense. How can I get my code to run when any cell on my worksheet is changed.
At the moment I am having to enter my data, then go into each cell in the range, press F2 to go into the cell then push enter, so the worksheet thinks I have amended that cell.
Any help would be appreciated. This is my code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("G3:G14")) Is Nothing Then
Select Case Target
Case Is = 0
icolor = 2
Case Is < 0.71
icolor = 3
Case 0.71 To 0.78
icolor = 45
Case Is > 0.78
icolor = 4
Case Else
icolor = 0
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
At the moment I am having to enter my data, then go into each cell in the range, press F2 to go into the cell then push enter, so the worksheet thinks I have amended that cell.
Any help would be appreciated. This is my code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("G3:G14")) Is Nothing Then
Select Case Target
Case Is = 0
icolor = 2
Case Is < 0.71
icolor = 3
Case 0.71 To 0.78
icolor = 45
Case Is > 0.78
icolor = 4
Case Else
icolor = 0
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub