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

On 18 Dec., 23:51, 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

Hi JJ

End Sub have to be the last line in your macro. See other changes too.

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
Target.Font.ColorIndex = 3
icolor = 3
Case 2
Target.Font.ColorIndex = 46
icolor = 46
Case 3
Target.Font.ColorIndex = 6
icolor = 6
Case 4
Target.Font.ColorIndex = 4
icolor = 4
Case 5
Target.Font.ColorIndex = 5
icolor = 5
Case 6
Target.Font.ColorIndex = 13
icolor = 13
Case Else
'Whatever
End Select


Target.Interior.ColorIndex = icolor
End If

End Sub

Regards,
Per

It's not working. It works if I manually type a number in a cell, but it
doesn't work where all my VLOOKUP formulas are. I tried re-entering my
formulas, recalculating, and even copying and just pasting the values. Oh,
and I did change one thing. I needed this to work for all of column A, not
just rows 1 - 10, so I typed

If Not Intersect(Target, Range("A:A")) Is Nothing Then (etc...)

I keep getting "Run-time error '13': Type mismatch". If I click "Debug"
then the debugger stops on Case 1.

"Per Jessen" wrote:

On 18 Dec., 23:51, 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

Hi JJ

End Sub have to be the last line in your macro. See other changes too.

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
Target.Font.ColorIndex = 3
icolor = 3
Case 2
Target.Font.ColorIndex = 46
icolor = 46
Case 3
Target.Font.ColorIndex = 6
icolor = 6
Case 4
Target.Font.ColorIndex = 4
icolor = 4
Case 5
Target.Font.ColorIndex = 5
icolor = 5
Case 6
Target.Font.ColorIndex = 13
icolor = 13
Case Else
'Whatever
End Select


Target.Interior.ColorIndex = icolor
End If

End Sub

Regards,
Per

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

Try this. Adjust colors to suit from color palette numbers.
Sub docolor()
For Each c In Range("a11:a16")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
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

Hi Don

You seem to have taken a different approach to my problem. I'm intrigued
but I don't really understand what your code is saying. I did expand the
range to include all of column A...maybe I screwed it up when I did that. I
entered:

Sub docolor()
For Each c In Range("a:a")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub

It does seem to respond to my VLOOKUP cells, but all I get is green (color
code 4). Was I supposed to add something else?

"Don Guillett" wrote:

Try this. Adjust colors to suit from color palette numbers.
Sub docolor()
For Each c In Range("a11:a16")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
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

Don't use the whole column and do use the on error statement. As written, it
is only looking up 1,2,3,4,5,6

Sub docolor()
On Error Resume Next
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
Hi Don

You seem to have taken a different approach to my problem. I'm intrigued
but I don't really understand what your code is saying. I did expand the
range to include all of column A...maybe I screwed it up when I did that.
I
entered:

Sub docolor()
For Each c In Range("a:a")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub

It does seem to respond to my VLOOKUP cells, but all I get is green (color
code 4). Was I supposed to add something else?

"Don Guillett" wrote:

Try this. Adjust colors to suit from color palette numbers.
Sub docolor()
For Each c In Range("a11:a16")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
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

Now we're getting somewhere! :) Just one glitch. It doesn't seem to execute
automatically. I right-clicked on the sheet and clicked on "View Code".
That's where I have your code pasted. The cells don't change colors unless I
go back in to this VB screen and click the little "play" button to run the
code. Can't this fire on it's own?

"Don Guillett" wrote:

Don't use the whole column and do use the on error statement. As written, it
is only looking up 1,2,3,4,5,6

Sub docolor()
On Error Resume Next
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
Hi Don

You seem to have taken a different approach to my problem. I'm intrigued
but I don't really understand what your code is saying. I did expand the
range to include all of column A...maybe I screwed it up when I did that.
I
entered:

Sub docolor()
For Each c In Range("a:a")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub

It does seem to respond to my VLOOKUP cells, but all I get is green (color
code 4). Was I supposed to add something else?

"Don Guillett" wrote:

Try this. Adjust colors to suit from color palette numbers.
Sub docolor()
For Each c In Range("a11:a16")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
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

It can be made to fire with the worksheet_calculate event but I don't
recommend it. I would assign to a shape from the drawing toolbar or a button
from the forms toolbar.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
Now we're getting somewhere! :) Just one glitch. It doesn't seem to
execute
automatically. I right-clicked on the sheet and clicked on "View Code".
That's where I have your code pasted. The cells don't change colors
unless I
go back in to this VB screen and click the little "play" button to run the
code. Can't this fire on it's own?

"Don Guillett" wrote:

Don't use the whole column and do use the on error statement. As written,
it
is only looking up 1,2,3,4,5,6

Sub docolor()
On Error Resume Next
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
Hi Don

You seem to have taken a different approach to my problem. I'm
intrigued
but I don't really understand what your code is saying. I did expand
the
range to include all of column A...maybe I screwed it up when I did
that.
I
entered:

Sub docolor()
For Each c In Range("a:a")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub

It does seem to respond to my VLOOKUP cells, but all I get is green
(color
code 4). Was I supposed to add something else?

"Don Guillett" wrote:

Try this. Adjust colors to suit from color palette numbers.
Sub docolor()
For Each c In Range("a11:a16")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
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

I know that conditional formatting limits me to 3 conditions,

Hi. Just to mention since I didn't see a version listed.
In Excel 2007, a nice option is to select "Conditional Formatting"
and select "Color Scales."
The Green-Yellow-Red is a nice option.
It also appears to match your color requirement where the low numbers are
red (ie 1), and working its way to green for the higher numbers (ie 6)

--
HTH
Dana DeLouis


"jjones" wrote in message
...
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