Possible to Apply More than Three Conditional Formats to a column?

  • Thread starter Thread starter MS
  • Start date Start date
M

MS

Let's say one column has a rating system numbered from 1 to 6. One wants to
format it so there is a different colored background for each number.

I can do that up to three settings, with "Conditional Format". (For
example--If the number in the cell is 1, make the background color yellow.
Then click the "Add" button, and one can add another format--perhaps if the
number in the cell is 2, make the background color blue.

For me that only works up to three settings, whereas I would need 6 for the
example above. After there are three such settings, the "Add" button is
grayed out.

Any way to add more such formats, to the same column, so that I could have a
different color-coding for each of those six numbers, for example?

Also, any way to extend that formatting to the whole row? (So that if the
number is 1 in that one column, its whole row (regardless of what appears in
other columns of that row) would become yellow?
 
You can only have 3 conditions, which allows if you include a default, but
no more I am afraid. To do so, you need VBA.

To colour the whole row, select the row, change the condition to Formula Is
and add a formula of =$A1=1 for instance.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob Phillips said:
You can only have 3 conditions, which allows if you include a default, but
no more I am afraid. To do so, you need VBA.

To colour the whole row, select the row, change the condition to Formula Is
and add a formula of =$A1=1 for instance.
Click to expand...

Sorry, I don't know programming, and am new at Excel, so I don't completely
understand this.

How exactly would I write that formula? For example, if the number 1 is in
the cell in that column, to turn that whole row yellow?
 
Select row 1
in the menu d

Format=>conditional format

Change the dropdown from "Cell Value is" to "Formula is"

in the next textbox (after Formula is) put in
=$A1=1
click on the format tab and select pattern tab, then select yellow

OK your way out.
 
Thanks again. I really appreciate how helpful you people are. It's really
nice to see. In some newsgroups people are always arguing, and making
sarcastic remarks when someone asks a question.

I'm surprised one cannot use more than three different conditional formats
in a column, so that for instance, if I want to color-code cells in a column
with one of six different colors, depending on the number in the cell
(1-6). No way around that limitation? (Of course one could code each cell
manually, but that would be time-consuming with a lot of cells. I mean, no
way to do that manually?) (I don't understand why such a limitation, as
there often might be more than three different conditions.)

Thanks again.
 
I can't tell you way the limit is three, but for any individual cell, the
limit is three (plus the original, so 4 actual formats are possible).
 
One way is to do this:

Right click on the Excel sheet tab and select 'View Code'

Copy the following into the sheet that just opened in the
VBA editor.



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count <> 1 Then Exit Sub 'action only if 1
cell is selected

If Target.Column <> 1 Then Exit Sub 'action only if
cell changed in column A

Select Case Target.Value 'evaluate the number that
you just entered

Case Is = 1
Target.EntireRow.Interior.ColorIndex = 3 'red

Case Is = 2
Target.EntireRow.Interior.ColorIndex = 38 'pink

Case Is = 3
Target.EntireRow.Interior.ColorIndex = 4 'green

Case Is = 4
Target.EntireRow.Interior.ColorIndex = 6 'yellow

Case Is = 5
Target.EntireRow.Interior.ColorIndex = 8 'majenta

Case Is = 6
Target.EntireRow.Interior.ColorIndex = 5 'blue

Case Else 'none of the above numbers
Exit Sub

End Select

End Sub




This will change the colour of any row where you enter the
number 1, 2, 3, 4, 5 or 6 in column A.

In the VBA editor, click on ColorIndex and press the F1
key to see what colours are available. Add more lines to
represent more numbers if necessary.

The line 'If Target.Column <> 1 Then Exit Sub' means that
the row colour will change only if you enter the required
number in column A. If you need to enter your numbers
into column B then change the 1 to 2, or for column C
change to 3 etc.

Hope this helps.

Kevin B
 
The limit of Conditional Formating is 3 conditions per cell, if you
color an entire row then each cell in that row uses that condition.
More on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Since you want six colors based on a value of 1-6 you cannot use C.F.
for that you will need to use VBA. Specifically an Event macro is what you
would need.

You have a contradiction in what you want to change the color of: the
row or the column. So I will go by the more specific statement

Since you don't specify a column, I will choose column B (column 2)
for you. You can find a list of colors by their colorindex in
http://www.mvps.org/dmcritchie/excel/colors.htm
if you have not changed them; otherwise, you will have to make up
your own list of colors.

Coding for your Event macro is based on
http://www.mvps.org/dmcritchie/excel/event.htm#case
but since you want to color the entire row, I will recode the macro
for you. Install your macro for the sheet you want to use it in by
right-click on the sheet tab, and paste the following code in.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2003-11-03 misc/newusers based on
' http://www.mvps.org/dmcritchie/excel/event.htm#case
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("B:B"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
'see color.htm and event.htm in same directory as
' http://www.mvps.org/dmcritchie/excel/excel.htm
vColor = xlAutomatic 'default is no color
Select Case cell.Value
Case 1
vColor = 34
Case 2
vColor = 36
Case 3
vColor = 39
Case 4
vColor = 41
Case 5
vColor = 38
Case 6
vColor = 37
End Select
' Application.EnableEvents = False 'should be part of Change macro
cell.EntireRow.Interior.ColorIndex = vColor
' Application.EnableEvents = True 'should be part of Change macro
Next cell
End Sub


Since you are changing the interioror color of a cell you will lose gridlines,
so if you want to see cell borders you will have to format borders for you
worksheet. Ctrl+A (select all cells), format (menu), cells, borders (outline & inside)

The Event macro will only color cells as you make a manual change to
the value in column B -- If you use a formula to determine value of 1-6
this code will not work for you. Also it will not change your existing
data. To do that you want to add an empty cell to each numeric value
in column B --- NOT TO THE ENTIRE COLUMN because that would take
a very long time to process every single cell in the column.

select an empty cell (no formula, no spaces, must be empty)
and copy it using Ctrl+C
Select column B, then to reduce selection to cells with numbers
Edit, GoTo, Constants, Numbers (uncheck Text, Logicals, errors)
Edit, Paste Special, Add

For future postings, would suggest the following as being more
acceptable.
use your name, it is a lot more friendly, and a lot more businesslike,
and it would help you to find your own postings in the archives
post only to one newsgroup, the same people read both groups
Either group is fine -- more on Excel newsgroups:
http://www.mvps.org/dmcritchie/excel/xlnews.htm

[clipped]
 
Back
Top