Greetings, again...

I am trying to get some code to change the background color of a cel
based on whether or not an adjacent cell contains a date. I understan
that I can not use the worksheet_change event on cells that get thei
value through a calculation, but is there a way to edit the code belo
so that the range cells (column I in this case) are colored and update
based on entries in columns F,G,H, & J?

I realize that what I am asking is a little vague, but basically I wan
to change the color or cells in column I based on what they say, whic
is derived from the following nested IF:
=IF(J50,IF(G5"","Corrected
Returned","Returned"),IF(G5"",IF(H50,"Corrected, Not Ye
Returned","Violation: See Notes"),IF(H50,"Completed, Not Ye
Returned",IF(F50,"Received, Not Yet Completed","Not Yet Received"))))

Then, based on what the function returns I want to shade the cells red
yellow, or green. I have the following code in the worksheet:
Private Sub Workbook_Open(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

Dim rng As Range

Set rng = Intersect(Target, Range("I:I"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "Returned"
cl.Interior.ColorIndex = 35
Case "Corrected & Returned"
cl.Interior.ColorIndex = 35
Case "Corrected, Not Yet Returned"
cl.Interior.ColorIndex = 36
Case "Completed, Not Yet Returned"
cl.Interior.ColorIndex = 36
Case "Received, Not Yet Completed"
cl.Interior.ColorIndex = 36
Case "Not Yet Received"
cl.Interior.ColorIndex = 3
Case "Violation: See Notes"
cl.Interior.ColorIndex = 36
Case Else
cl.Interior.ColorIndex = 0
Exit Sub
End Select
Next cl
End If

End Sub

But, this code does not update automatically; I have to enter the cell
in column I and press F2, then Enter to force the macro to update them
Is there a way to make the shade change without having to edit th
target cell each time I enter a date in G,H, or J?

I can attach the worksheet if I need to so you can see what I am doing
but will wait on that for now...Thanks in advance..

--
Message posted from http://www.ExcelForum.com