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.