OnSheetActivate or something...

[jpendegraft]

I am running Excel XP and am trying to call an "extended" conditiona
formatting procedure (more than 3 variables) but would like to run i
when the user activates the particular sheet.

The sheet is being populated via links from other worksheets and i
simple the "final report" that I am trying to add some "color codin
to"...

I have test the code on a "change event" and it works fine, but
cannot get it to work when the user focus....The change event is bein
is a bit different as the cell contains a Value and not a formul
(reference).

I feel like I have at least an error in my event and perhaps I need t
make some modifications to accomadate the reference formulas rathe
than the values....

Below is my code...

Private Sub Worksheet_OnSheetActivate(ByVal Target As Range)
ActiveSheet.Unprotect

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:AM4")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case "<<Brand>>":
.Font.Color = RGB(0, 0, 0)
.Font.Size = 12
.Font.FontStyle = "Regular"

Case "Labatt":
.Font.Color = RGB(64, 40, 170)
.Font.Size = 12
.Font.FontStyle = "Bold"

Case "RR/RGL":
.Font.Color = RGB(48, 122, 8)
.Font.Size = 12
.Font.FontStyle = "Bold"

Case "RGL":
.Font.Color = RGB(48, 200, 8)
.Font.Size = 12
.Font.FontStyle = "Bold"

Case "Stella Artois":
.Font.Color = RGB(243, 100, 10)
.Font.Size = 12
.Font.FontStyle = "Bold"

Case "Bass":
.Font.Color = RGB(126, 3, 49)
.Font.Size = 12
.Font.FontStyle = "Bold"

Case "Beck's":
.Font.Color = RGB(16, 133, 27)
.Font.Size = 12
.Font.FontStyle = "Bold"

Case "Global 4"
.Font.Color = RGB(255, 0, 0)
.Font.Size = 12
.Font.FontStyle = "Bold"

Case "Select":
.Font.Color = RGB(114, 111, 123)
.Font.Size = 12
.Font.FontStyle = "Bold"

End Select
End With

CleanUp:
Application.EnableEvents = True

ActiveSheet.Protect , True, True, True, True
End Su

 

[Tom Ogilvy]

The name of the event is
Private Sub Worksheet_Activate()

End Sub

for code in the sheet module. If the thisworkbook module (a workbook level
event)

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

Neither event is named OnSheetActivate nor to either take a range argument.
Unfortunately, only the standard events are supported - you can't make up
your own events. You should always declare/build your events by using the
dropdowns at the top of the module to insert the declaration. This
minimizes the chances for such errors.

You code looks like it would work in a change event. To use it in the
activate event, there will be no target argument, so you would have to
address each cell that needs to be colored.

for each cell in Range("A1:A10,B5,C11:C15,D20,F1:E5")
Select Case cell.Value

as an example.