Case Statement for Changing Font AND Background Color

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
 
P

Per Jessen

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
Click to expand...

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
 
G

Gord Dibben

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
 
D

Don Guillett

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
 
J

jjones

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

jjones

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?
 
D

Don Guillett

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
 
J

jjones

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?
 
D

Don Guillett

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

jjones

Thanks Don. I guess I'll just make this code part of some other macros that
will be running. Appreciate the help...

--JJ
 
D

Dana DeLouis

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conditional formating 1
Select Case Statement - Help Font Color 1
Modify sub to work on cells with formulas 2
Amend sheet sub to work if input range contains formulas 2
Pass Cell Value & Formatting to Another Worksheet 1
Conditional Formatting- Refresh 7
Case statement comparing two ranges 4
VBA Code to run when any cell is updated 0

Top