Conditional Formatting - more than 4 cell colors

[sharakbh]

I've read through MANY of the posts for doing more than 4 conditional formats
in Excel. I've tried using several of the various examples provided by
previous posters (entering code onto the worksheet tab "view code"), but
nothing seems to work. Several people suggest using an add-in (xld cfP), but
the website provided doesn't appear to be working.

Here's my simple scenario . . .

I have a row of data containing text (people's names as an example), and the
text (person's name) can change depending on the formula in the cells within
this row. I would like the cells to change color (not the font, just the
interior color of the cell) based on the text (person's name) listed in each
cell. For example:

Carol
Bob
Tom
Jerry
Sue
Sally
Larry

So for Carol, I would like the cell interior color to be red, bob to be
blue, tom to be yellow, Jerry to be green, etc. (at this point, the colors
do not matter, this is just an example).

I've used several different scenarios provided by previous posters, but none
seem to work, so I believe I may not be doing something correctly.

I am using Excel 2003, and am an intermediate user. I've never used the VB
code area before. Here's what I'm doing . . .

I am on the worksheet with my data (people's names) in Row C (for example).
I go to the tab, right click, and select View Code. A new screen appears
with a blank window. I then paste in the code provided by a previous poster.
I adjust the code to match my Row C, people's names, etc. I click save. I
then go back to my excel worksheet, and no changes appear. What else do I
need to do? Is there an add-in or something I need to load in order for the
code change to appear? Help! Thanks in advance!

Here's one of the sample codes provided by previous poster that I tried to
use:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("C1:C45")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Carol"
Target.Interior.ColorIndex = 5
Case "Steve"
Target.Interior.ColorIndex = 10
Case "Lulu"
Target.Interior.ColorIndex = 6
Case "Shara"
Target.Interior.ColorIndex = 46
Case "Lilian"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub

 

[JP]

If your information is in a row, you have to adjust the Workrange reference
to a row. C1:C45 only covers column C. You want something like A3:IV3. I
tested out your sample code below and it works fine, as long as you type
something in C1:C45.


HTH,
JP

 

[sharakbh]

Sorry, I meant columns! So, it still doesn't work for me. This is my
first time going to the VB tab to enter code. As I mentioned, I'm not a
total power user (coder), just an "avg Joe" with intermediate skill level.
Is there something I need (add-in) in order to make this work? Not sure why
it won't work, when it seems to work for previous posters. Thx for your
help!

 

[JP]

You would simply change the range in WatchRange to cover the cells
where you are placing the names. For example if you were typing the
names in cells A3:I3, change it to

Set WatchRange = Range("A3:I3")

Now type a name in one of those cells. If it still doesn't change
color, can you post the EXACT code you are using?

HTH,
JP

 

[sharakbh]

I'm getting there . . . I opened up a brand new spreadsheet to try this
again. So I posted in the following code on to the VB page and saved it.
When I went to the worksheet to Cells C1:C45 and manually typed in Carol,
Shara, etc, the cell changed to a color. YEAH! . . . BUT . . . in my
example, for the cells that already have data in them, what is the
workaround/coding to make those change to a color as well (w/o cutting and
pasting my data in order for the cells to change color)? Again, thanks for
your help JP!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("C1:C45")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Carol"
Target.Interior.ColorIndex = 5
Case "Steve"
Target.Interior.ColorIndex = 10
Case "Lulu"
Target.Interior.ColorIndex = 6
Case "Shara"
Target.Interior.ColorIndex = 46
Case "Lilian"
Target.Interior.ColorIndex = 45
End Select
End If
End Sub
--
sharakbh

You would simply change the range in WatchRange to cover the cells
where you are placing the names. For example if you were typing the
names in cells A3:I3, change it to

Set WatchRange = Range("A3:I3")

Now type a name in one of those cells. If it still doesn't change
color, can you post the EXACT code you are using?

HTH,
JP

 

[JP]

That's different. You would need something like this. First select the
cells with the existing information you want to highlight.

Sub ConvertExisting()
Dim rng As Excel.Range
For Each rng In Selection
Select Case rng
Case "Carol"
rng.Interior.ColorIndex = 5
Case "Steve"
rng.Interior.ColorIndex = 10
Case "Lulu"
rng.Interior.ColorIndex = 6
Case "Shara"
rng.Interior.ColorIndex = 46
Case "Lilian"
rg.Interior.ColorIndex = 45
End Select
Next rng
End Sub


This is just some air code, but it should work.

HTH,
JP