A few revisions should help.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
Select Case Target
Case 1
.Font.ColorIndex = 3
icolor = 3
Case 2
.Font.ColorIndex = 46
icolor = 46
Case 3
.Font.ColorIndex = 6
icolor = 6
Case 4
.Font.ColorIndex = 4
icolor = 4
Case 5
.Font.ColorIndex = 5
icolor = 5
Case 6
.Font.ColorIndex = 13
icolor = 13
Case Else
'Whatever
End Select
End With
Target.Interior.ColorIndex = icolor
End If
End Sub
Gord Dibben MS Excel MVP
On Tue, 18 Dec 2007 14:51:00 -0800, jjones
wrote:
Column A of my spreadsheet contains a VLOOKUP formula all the way down that
returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6).
I want these numbers there for sorting purposes, but I don't want to actually
see them. Instead I want to see a "color code" all the way down. So if the
value is 1, then I want the background color and the font for that cell to be
red. If 2, then orange, etc...
I know that conditional formatting limits me to 3 conditions, but I'm sure
that I can write some sort of CASE statement to do the same thing. I've
found several posts similar to what I'm looking for, but not exact. I tried
to piece them together, but since my VB skills leave alot to be desired, I
need some help to pull this off. What I have is something like this:
_________________________________________________ _____
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1
Font.ColorIndex = 3
icolor = 3
Case 2
Font.ColorIndex = 46
icolor = 46
Case 3
Font.ColorIndex = 6
icolor = 6
Case 4
ColorIndex = 4
icolor = 4
Case 5
Font.ColorIndex = 5
icolor = 5
Case 6
ColorIndex = 13
icolor = 13
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
_________________________________________________ _____
It doesn't seem to do anything. Can someone tell me how this code should be
written?
Thanks in advance,
JJ