VBA

[FP Novice]

I have these macro's built to run on my (Lookup) Sheet1. My trouble is that
they work fine when I play each individual macro; however, I cannot get them
to run automatically. My security is set to low and I have 'Trust all
installed add-ins and templates' checked.

MS Excel 2003

Thanks,
Todd



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 = 2 '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

Sub Color_J6()
Dim Num As Long
Dim rng As Range
Set rng = Me.Range("J6")
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 = 2 '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

Sub Color_K6()
Dim Num As Long
Dim rng As Range
Set rng = Me.Range("K6")
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 = 2 '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

 

[Barb Reinhardt]

I'm guessing you want to use a Worksheet Change event. You can read more
about events here

http://www.cpearson.com/excel/Events.aspx
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[Don Guillett]

Try it this way

Sub Colorif()
Dim c
For Each c In Array("g6", "j6", "k6")
Dim Num As Long
Dim rng As Range
Set rng = Range(c)
' 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 = 2 '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
Case Else
Num = 0
End Select
'Apply the color
rng.Font.ColorIndex = Num
'Application.EnableEvents = True
Next c
End Sub

 

[Don Guillett]

If that is indeed the case, right click sheet tab>view code>copy/paste this

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("g6, j6, k6")) Is Nothing Then Exit Sub
Dim Num As Double
Application.EnableEvents = False
'Determine the color
Select Case UCase(Target)
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 = 2 '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
Case Else
Num = 0
End Select
'Apply the color
Target.Font.ColorIndex = Num
Application.EnableEvents = True
End Sub

 

[FP Novice]

Thanks Barb, nice link and the Worksheet_Change worked...

 

[FP Novice]

Perfect Don, much cleaner than what I had. I did leave the opening string:
Private Sub Worksheet_Change(ByVal Target As Range)

The rest I converted to what you gave me, thanks.

I do have another question, how can I make the macro work when G6, J6, and
K6 are protected cells, since they hold functions I want protected.

Thanks again,
Todd

 

[Don Guillett]

Show your FINAL code. A worksheet change event works on changing the cell so
it should be UN protected.