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"
> <Cynthia@discussions.microsoft.com> 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" <Cynthia@discussions.microsoft.com> schrieb im Newsbeitrag
> >> news:C3B91460-50EF-4002-8B14-209E72B2D0DA@microsoft.com...
> >> > 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" <Cynthia@discussions.microsoft.com> schrieb im
> >> Newsbeitrag
> >> > > news:D9099B88-E143-40AF-86F4-9397A24AF89D@microsoft.com...
> >> > > > 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.
> >> > >
> >> > >
> >>
> >>
>
>