Use the calculation event instead of change.

You will have to update all the cells regardless of what has changed, so the
Target variable will not be used.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi group
I am using a worksheet event code to handle 3 condition formatting. The
criteria cells are all linked to other sheets. How can I get the formatting
updated since there is no manual change on the sheet? Also, I would like to
only format the cells 0 in addition to the other criteria.

A1:A9 is car, train, plane, bike, or boat
B1:H1 is 0-10
If A1 is Car and B1 0 then RED
If A1 is Car and B2=0 then no format

My Code.....

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A9")) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "Car": .Offset(0, 7).Resize(1, 7).Interior.ColorIndex
= 3
Case "Boat": .Offset(0, 7).Resize(1,
7).Interior.ColorIndex = 5
Case "Bike": .Offset(0, 7).Resize(1,
7).Interior.ColorIndex = 10
Case "Train": .Offset(0, 7).Resize(1,
7).Interior.ColorIndex = 19
Case "Plane": .Offset(0, 7).Resize(1,
7).Interior.ColorIndex = 20
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Thanks in advance