Conditional Formatting - Getting pass the 3 condition limit

[Guest]

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.

 

[Frank Kabel]

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

 

[Frank Kabel]

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

 

[Gord Dibben]

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

 

[Guest]

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]

I don't experience your problem

Selecting a value from a DV drop-down triggers the sheet_change event.

BTW........colorindex for blue is 5, not 10


Gord

 

[Bob Phillips]

What Excel version?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

it's excel 2000

What Excel version?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Could have explained it if was Excel 97, but not 2000. Sorry.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Judy Rose]

I also need this help and am not sure how to access the VBE in the system, in
addition I am referencing a list of 15 numbers each corresponds to a
different background and need the background to fill an entire row selection
based on the number in the given column. This is for color tracking of
application data as the application works its way through the process each
number corresponds to a step in the process and I want the row of data to
change automatically when I change the number on the row.

Thank you.

 

[Gord Dibben]

Right-click on the worksheet tab and "View Code"

Copy/paste this code into that module.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A") 'adjust to suit your range
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'your 15 numbers will go here
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15) 'you need 15 colorindex numbers
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
End Sub

This code has been tested with 10 numbers in the vals array
The nums array is the colorindex numbers from the Excel color palette

You will have to adjust and edit to suit.

For a list of the colorindex numbers see David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm

When editing is complete, Alt + q to return to the Excel window.

As always, do all of this on a copy of the worksheet or workbook.


Gord Dibben MS Excel MVP

 

[Judy Rose]

Thank you. Only issue I had was coming up with enough colors different
enough to cover all my categories. Is there a way I could specify if a
certain color fill is used to also use a set pattern or font color? I
understood where your programming came from but wasn't sure where I could
indicate if icolor= (certain value) Then font for entire row = Bold/Italics,
etc.....How to get the program to acknowledge the fact that for a given color
for a row I need the change in font so it will be more visible. If this is
possible would you let me know.

Again, thank you for the help, it worked really well.

 

[Gord Dibben]

I would go with darker fill colors then color the font white.

Make changes as such...............

Next
If icolor <> 0 Then
With rr
.Interior.ColorIndex = icolor
.Font.ColorIndex = 2
.Font.Bold = True
End With
End If
Next

BTW.........Your original post stated 15 numbers.

Excel has 56 colors in the color palette.


Gord

 

[Judy Rose]

Originally I did only have 15, then realized I forgot one type of application
condition, where do I insert the code for the font, is it a modification of
the following; (sorry have a mental block when it comes to programming, you'd
think after 20+ years i wouldn't, but I do....)
Next
If icolor > 0 Then
rr.EntireRow.Interior.ColorIndex = icolor
End If
Next
End Sub

Again thank you for your assistance

 

[Judy Rose]

Just reread the code and figured out what you were saying, so I gather than
that I cannot do another sub of the original sub to specify not only to
change the color to a specific for each icolor, but with certain icolor to
change the font on only those particular ones that are too dark to having the
font go white. I just had one that I used in the original that the color was
too dark and it was only that particular color I wanted the change in the
font. In my previous worksheets I had done the change of the rows to colors
manually each time and so I had certain patterns used with certain colors to
ensure they had visible fonts, however didn't see a way to do a case by case
so that for each value the fill would be the base color, plus a given
pattern, plus a given font, I saw something with specification for cases, but
with the array the formula worked better for my needs.

 

[Gord Dibben]

If you want to change the font color to white as suggested, just alter the code
as I posted in my last reply.

If you want to have each case a different colored font.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A50")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15)
fnts = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
For Each rr In r
icolor = 0
jcolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
jcolor = fnts(i)
End If
Next
If icolor > 0 And jcolor > 0 Then
rr.Interior.ColorIndex = icolor
rr.Font.ColorIndex = jcolor
End If
Next
End Sub


Gord

 

[Judy Rose]

Thank you again, guess you didn't see my follow up post where I was asking
precisely how to do what you just described. Thank you again for all of your
help. This should work very nicely.

 

[Gord Dibben]

Thanks for the feedback.

You can alter the fnts = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11)

fnts = Array(2, 3, 2, 2, 2, 2, 5, etc) to match just the darker or lighter nums
array

A lot of fiddling but can be done.


Gord

 

[Judy Rose]

Ok, I tinkered, got the thing to work on the column where the number is
specified that the nums and fnts refer to, also used the with rr that you set
up to indicate for all fonts to use bold and that worked very well, what I
don't understand with the code is what did I do that now for the font it only
shows in the first column, I need the font to also carry across the
row.....didn't see where to put in the reference for jcolor to carry same as
icolor. Not sure where I went wrong....

again thank you for your assistance.

 

[Gord Dibben]

Next
If icolor > 0 And jcolor > 0 Then
With rr.EntireRow
.Interior.ColorIndex = icolor
.Font.ColorIndex = jcolor
End With
End If
Next
End Sub



Gord