Worksheet_Change Event

[cmcfalls]

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(J5>0,IF(G5>"","Corrected
Returned","Returned"),IF(G5>"",IF(H5>0,"Corrected, Not Ye
Returned","Violation: See Notes"),IF(H5>0,"Completed, Not Ye
Returned",IF(F5>0,"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..

 

[kkknie]

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

 

[JWolf]

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

 

[JWolf]

Check your spelling more carefully than I did, too.