Thank you, that worked and made complete sense. Both your and Chip's
explanations are great. I have another question: Why does protecting a cell
override the macro? That is, once the cells are protected and locked the
macro no longer runs.

"Bob Phillips" wrote:

Try

Private Sub Color_G6()
Dim Num As Long
Dim rng As Range
Set rng = Activesheet.Range("G6")
On Error GoTo endit
Application.EnableEvents = False
'Determine the color
Select Case UCase(rng.Value)
Case Is = "BLUE": Num = 5 'blue
Case Is = "ORANGE": Num = 45 'orange
Case Is = "GREEN": Num = 10 'green
Case Is = "BROWN": Num = 53 'brown
Case Is = "SLATE": Num = 15 'slate
Case Is = "WHITE": Num = 1 'black
Case Is = "RED": Num = 3 'red
Case Is = "BLACK": Num = 1 'black
Case Is = "YELLOW": Num = 6 'yellow
Case Is = "VIOLET": Num = 54 'violet
Case Is = "ROSE": Num = 38 'rose
Case Is = "AQUA": Num = 42 'aqua
End Select
'Apply the color
rng.Font.ColorIndex = Num
endit:
Application.EnableEvents = True
End Sub

Me refers to a worksheet, workbook, form or some other class, and has to be
used within that code module..

--
__________________________________
HTH

Bob

"FP Novice" wrote in message
...
I am getting an error with the Me object, I have virtually no VBA
experience
and as such I have no idea what other object to use here...

Private Sub Color_G6()
Dim Num As Long
Dim rng As Range
Set rng = Me.Range("G6")
On Error GoTo endit
Application.EnableEvents = False
'Determine the color
Select Case UCase(rng.Value)
Case Is = "BLUE": Num = 5 'blue
Case Is = "ORANGE": Num = 45 'orange
Case Is = "GREEN": Num = 10 'green
Case Is = "BROWN": Num = 53 'brown
Case Is = "SLATE": Num = 15 'slate
Case Is = "WHITE": Num = 1 'black
Case Is = "RED": Num = 3 'red
Case Is = "BLACK": Num = 1 'black
Case Is = "YELLOW": Num = 6 'yellow
Case Is = "VIOLET": Num = 54 'violet
Case Is = "ROSE": Num = 38 'rose
Case Is = "AQUA": Num = 42 'aqua
End Select
'Apply the color
rng.Font.ColorIndex = Num
endit:
Application.EnableEvents = True
End Sub


Thanks,
Todd