trying to apply conditional formatting to rows based on a cell's formula results, using vba code. (there are more than three conditions, so i can't use conditional formatting).

i got the following code through a previous post that works ok if i go to the cell with the formula, edit the text, then press return to update the results... however, if instead, i change one of the values that the formula is based on (in order to test the result with a different value), the formatting is not updated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Intersect(Target, Range("S:S"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range

For Each cl In rng
Select Case cl.Text
Case "Closed"
cl.EntireRow.Interior.ColorIndex = 35
Case "Canceled"
cl.EntireRow.Interior.ColorIndex = 40
Case "Open Mod"
cl.EntireRow.Interior.ColorIndex = 36
Case "New Award"
cl.EntireRow.Interior.ColorIndex = 34
Case Else
cl.EntireRow.Interior.ColorIndex = 0

Exit Sub
End Select
Next cl
End If

End Sub

Anyone know how to make this work?

TIA!
Jill.

Changing a cell that is not within that range will not trigger the change
event. The best you can do is to test the calculate event, but then you
would need some algorithm for determining how much of column S that you
want to update.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jill" wrote in message
...
trying to apply conditional formatting to rows based on a cell's formula
results, using vba code. (there are more than three conditions, so i can't
use conditional formatting).

i got the following code through a previous post that works ok if i go to
the cell with the formula, edit the text, then press return to update the
results... however, if instead, i change one of the values that the formula
is based on (in order to test the result with a different value), the
formatting is not updated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Intersect(Target, Range("S:S"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range

For Each cl In rng
Select Case cl.Text
Case "Closed"
cl.EntireRow.Interior.ColorIndex = 35
Case "Canceled"
cl.EntireRow.Interior.ColorIndex = 40
Case "Open Mod"
cl.EntireRow.Interior.ColorIndex = 36
Case "New Award"
cl.EntireRow.Interior.ColorIndex = 34
Case Else
cl.EntireRow.Interior.ColorIndex = 0

Exit Sub
End Select
Next cl
End If

End Sub

Anyone know how to make this work?

TIA!
Jill.