Worksheet Event Code

[Guest]

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

 

[Tom Ogilvy]

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

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.

 

[Guest]

THanks for your response Tom
I am relatively new to VBA, and I am not familiar with this. How would I modify my code to accomplish this
Thanks
J