I had a similar problem and I followed your advice for this, it worked
wonderfully. I am having one problem though.
My spreadsheet has a column that uses a Validation List, it is possible to
choose from 6 choices. G, Y, R, N/A, No Criteria, and a blank cell. I used
the VBA code you suggested to color G green, Y yellow, R red, N/A blue, No
Criteria white, and the blank cell blank.

The problem is that since the G, Y, R, etc are chosen from a list, the cell
does not change color unless I actually enter the cell (F2), so if the cell
is red, and I choose G (from the list), it stays red. Is there a way to
refresh the screen in a way, so that the formatting works real time? I even
tried making another cell equal that cell, but the same thing happens.
Thanks.


"Gord Dibben" wrote:

Cynthia

Not Frank but......

The third and fourth line are all one line.

Place a <space _ after the word "Nothing"

If Intersect(Target, Me.Range("C1:C300")) Is Nothing _
Then Exit Sub

I would also stick an Option Compare Text above the Sub to make the entries
case-insensitive.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C1:C300")) Is Nothing _
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
Case "Green": .Interior.ColorIndex = 4
Case "Yellow": .Interior.ColorIndex = 6
Case "Brown": .Interior.ColorIndex = 9
Case "Black": .Interior.ColorIndex = 1

End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub

Gord Dibben Excel MVP

On Tue, 31 Aug 2004 11:57:02 -0700, "Cynthia"
wrote:

Frank you've been so helpful I'm hoping you can give me one more hint.
Here is the script I have based on your answer below. In my spreadsheet the
column I want to change colors is C: so I changed the range below from what
you had.
In my editor I get a compile error. The 3rd & 4th lines below (If
intersect....then sub) are highlighted in red. Not sure of what I should do
here to get this to work.
What I'm interested in is having the whole column C: (not just a range) be
formatted in this manner.

Can you help one more time.

Thanks,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C1:C300")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
Case "Green": .Interior.ColorIndex = 4
Case "Yellow": .Interior.ColorIndex = 6
Case "Brown": .Interior.ColorIndex = 9
Case "Black": .Interior.ColorIndex = 1

End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub

"Frank Kabel" wrote:

Hi
The following will color the entry in cell A1:A100 based
on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


For more about event procedures see:
http://www.cpearson.com/excel/events.htm



--
Regards
Frank Kabel
Frankfurt, Germany

"Cynthia" schrieb im Newsbeitrag
...
Frank,
I don't know VBA programming. I'm new to the user discussion group.
Is there
an area with scripts that I could search?

"Frank Kabel" wrote:

Hi
more conditions are only available if you use VBA. Would this be a
way
for you?.

--
Regards
Frank Kabel
Frankfurt, Germany

"Cynthia" schrieb im
Newsbeitrag
...
I have a spreadsheet where I have set a data validation for the
colum
to
select from a list. The list has names of colors. (Red, yellow,
etc)
A total
of 6 colors. I then set a conditional format for the column to
change
the
background of the cell to the selected color. (The word "red"
displays in
cell and background color of cell is red).

Excel limits the number of conditions to 3. I have 6 color
condtions
(red,
yellow, green, blue, brown, black). How do I get around the 3
limit
condition
where I can set all 6 colors to change when the text is selected
from
the
drop down.