I used the code below to extend conditional formatting to 4 cases. It works
great on the cells that I manually enter values in, but not for the cells in
row 15 that contain the formulas. The formula values do update, but the cell
colors don't change when I delete the values above and re-enter new ones. It
worked fine the very first time I entered values, but there must be
additional code I need to make the formula cells realize that they're
supposed to change color when their calculated value changes. What am I
missing?? Thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "b9:p15"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is = 4: .Interior.ColorIndex = 5 'blue
Case Is = 3: .Interior.ColorIndex = 10 'green
Case Is = 2: .Interior.ColorIndex = 6 'yellow
Case Is = "": .Interior.ColorIndex = None
Case Is = 0: .Interior.ColorIndex = 3 'red
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

I never have done this before so i don't know how far down the rabbit hole it
will go as far as complexity of dependents, but it works on some simple one
dependent formulas. What i did was said that when a target changes, check the
dependents as well and update their colors. Let me know, interested to see
what you get.

Select Case .Dependents.Value
Case Is = 4: .Dependents.Interior.ColorIndex = 5 'blue
Case Is = 3: .Dependents.Interior.ColorIndex = 10 'green
Case Is = 2: .Dependents.Interior.ColorIndex = 6 'yellow
Case Is = "": .Dependents.Interior.ColorIndex = None
Case Is = 0: .Dependents.Interior.ColorIndex = 3 'red
End Select

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Joanna" wrote:

I used the code below to extend conditional formatting to 4 cases. It works
great on the cells that I manually enter values in, but not for the cells in
row 15 that contain the formulas. The formula values do update, but the cell
colors don't change when I delete the values above and re-enter new ones. It
worked fine the very first time I entered values, but there must be
additional code I need to make the formula cells realize that they're
supposed to change color when their calculated value changes. What am I
missing?? Thanks!
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "b9:p15"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is = 4: .Interior.ColorIndex = 5 'blue
Case Is = 3: .Interior.ColorIndex = 10 'green
Case Is = 2: .Interior.ColorIndex = 6 'yellow
Case Is = "": .Interior.ColorIndex = None
Case Is = 0: .Interior.ColorIndex = 3 'red
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub