J
jjones
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
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