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

--
jpendegraf

-----------------------------------------------------------------------
jpendegraft's Profile: http://www.excelforum.com/member.php...nfo&userid=570
View this thread: http://www.excelforum.com/showthread.php?threadid=26134