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

I believe you can use the worksheet_change event, just do it when one o
the cells that effects the if statement is changed (in your case F,G,H
& J).

Something like:

Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 or Target.Column = 7 or Target.Column = 8 or Target.Colum = 10 Then

'Your code Here

End I
-------------------



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

Use Format, Conditional Format... instead:
Condition 1: formula is: =or(I5="Returned",I5="Corrected & Returned");
set format color 35
click add
Condition 2: formula is: =or(I5="Correctd, Not Yet
Returned",I5="Completed, Not Yet Returned",
I5= "Received, Not Yet Completed",I5= "Violation: See Notes"); set
format color 36
click add
Condtion 3: cell value: = "Not Yet Received"; set format color 3

cmcfalls < wrote:
Greetings, again...

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

I realize that what I am asking is a little vague, but basically I want
to change the color or cells in column I based on what they say, which
is derived from the following nested IF:
=IF(J50,IF(G5"","Corrected &
Returned","Returned"),IF(G5"",IF(H50,"Corrected, Not Yet
Returned","Violation: See Notes"),IF(H50,"Completed, Not Yet
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 cells
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 the
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/

Check your spelling more carefully than I did, too.

JWolf wrote:

Use Format, Conditional Format... instead:
Condition 1: formula is: =or(I5="Returned",I5="Corrected & Returned");
set format color 35
click add
Condition 2: formula is: =or(I5="Correctd, Not Yet
Returned",I5="Completed, Not Yet Returned",
I5= "Received, Not Yet Completed",I5= "Violation: See Notes"); set
format color 36
click add
Condtion 3: cell value: = "Not Yet Received"; set format color 3