VBA and Text Color

[FP Novice]

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

 

[Bob Phillips]

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..

 

[Chip Pearson]

The "Me" object can be used only in Object Modules (Class modules,
ThisWorkbook, UserForms, and the Sheet modules). You cannot use it in
regular code modules. Me always refers to the object in which it
appears. So, for example, if you use it in Sheet1, it refers to
Sheet1. Use it in ThisWorkbook, it refers to ThisWorkbook.

If you need to specify a worksheet for your range, use something like

Set rng = Worksheets("Sheet2").Range("G6")

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[FP Novice]

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.