Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Starting/Stopping Worksheet Event Code | Excel Discussion (Misc queries) | |||
Where?Worksheet code module or Worksheet_SelectionChange event han | Excel Worksheet Functions | |||
Worksheet Event Code | Excel Worksheet Functions | |||
event in worksheet switches to VB code window | Excel Programming | |||
help worksheet event code | Excel Programming |