Worksheet Event Code
 

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

Worksheet Event Code
 

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

Worksheet Event Code
 

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

"Tom Ogilvy" wrote:

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