Worksheet_Change event won't fire to execute Macro???

[jpdill5]

A little help would be appreciated...

I have a worksheet where I am trying to color-code cells based on th
value entered into the cell. For example, if I enter the value o
"CTC" in cell E125, I would like to set the background color of th
cell to light blue. If I enter a different value of "LAT" in the sam
cell, I would like the background color to be light yellow.

Since I have about 7 different cases where I want to color cells,
can't use the conditional formatting. That being said, I believe th
best way to do this is with a Worksheet_Change event, and have create
the following and placed it in the Worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("E123:AC145")) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "LAT"
.ColorInterior.ColorIndex = 36
Case "CTC"
.ColorInterior.ColorIndex = 34
Case "HOL"
.ColorInterior.ColorIndex = 44
Case "VAC"
.ColorInterior.ColorIndex = 7
Case "INFT"
.ColorInterior.ColorIndex = 11
.Font.ColorIndex = 2
Case "UNICA"
.ColorInterior.ColorIndex = 11
.Font.ColorIndex = 2
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


And it does not seem to work when I enter/change a value in one of th
cells listed in the range. I don't know if there is something else
need to do in order to enable this functionality? Can anyone help?

Thanks,

Jef

 

[Don Guillett]

try this instead. You had several errors.
lcase, ColorInterior.ColorIndex = 36,etc

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:C14")) Is Nothing Then
With Target
Select Case UCase(Target)
Case "LAT"
.Interior.ColorIndex = 36
.Font.ColorIndex = xlAutomatic
Case "CTC"
..Interior.ColorIndex = 34
.Font.ColorIndex = xlAutomatic
Case "HOL"
..Interior.ColorIndex = 44
.Font.ColorIndex = xlAutomatic
Case "VAC"
..Interior.ColorIndex = 7
.Font.ColorIndex = xlAutomatic
Case "INFT"
..Interior.ColorIndex = 11
Font.ColorIndex = 2
Case "UNICA"
..Interior.ColorIndex = 11
..Font.ColorIndex = 2
Case Else
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = xlAutomatic
End Select
End With
End If
End Sub

 

[jpdill5]

Don -

Thanks for the help - worked like a charm. After staring at this lon
enough, the obvious LCASE/UCASE error slipped right by me.

Jef