Conditional Formatting - more than 3?

[penri0_0]

Hi,

Is there any way i can format a cell with more than 3 conditions?

Thanks in advance

 

[Dave O]

Depends on your conditions. Do you need more than three formats, or do
you need more than three conditions? Please post the conditions.

 

[penri0_0]

Hi Dave
I have four ranges:

0 to 9999
10,000 to 19,999
20,000 to 29,999
and 30,000 to 39,999

on an invoice spreadsheet. If the value of an invoice falls within one
of these ranges i want it to shade the cell a different colour.

thanks

 

[Lucas Lehmer]

Hello penri0_0,

Apparently we have the same problem.
Maybe you can learn something from my posting here:
http://www.excelforum.com/showthread.php?t=542791

Succes!

 

[penri0_0]

Thanks Lucas, i'll give it a go although i've got little experience
programming!

 

[Guest]

The only thing I know you can do is format the cell to your first condition
under Format Cells the conditionally format for the other three. Here is a
link posted here before but I never tried.

http://www.xldynamic.com/source/xld.CFPlus.Download.html


TC

 

[Saruman]

If you format all the cells with a base colour before adding the conditional
formats then you will have 4 colours available

Saruman

 

[Guest]

Hi Saruman,

Could you explain how to format the cell with the base colour? Not sure
what you mean?

I need four colors. I have already green, yellow, and red. I just need one
more color which would be white.

1. if it is less than 1 (100%) then green
2. if it is greater than 1 but less than 1.25 then yellow
3. if it is greater than 1.25 then red
4. if it is "NA" then white

I apply the conditional formatting to a single column that has a forumla in
each cell.

Please advise. Thanks!
Amy

If you format all the cells with a base colour before adding the conditional
formats then you will have 4 colours available

Saruman

 

[Gord Dibben]

Amy

The fourth is the default "no color".


Gord Dibben MS Excel MVP

Hi Saruman,

Could you explain how to format the cell with the base colour? Not sure
what you mean?

I need four colors. I have already green, yellow, and red. I just need one
more color which would be white.

1. if it is less than 1 (100%) then green
2. if it is greater than 1 but less than 1.25 then yellow
3. if it is greater than 1.25 then red
4. if it is "NA" then white

I apply the conditional formatting to a single column that has a forumla in
each cell.

Please advise. Thanks!
Amy

If you format all the cells with a base colour before adding the conditional
formats then you will have 4 colours available

Saruman

 

[Guest]

Ok. But, how do I apply this "default" to a column that already has
conditional formatting set? Since the three conditions are already set, where
or how to tell the established formatting to us no color if it meets none of
these conditions?

Thanks for replying!

Amy

The fourth is the default "no color".


Gord Dibben MS Excel MVP

Hi Saruman,

Could you explain how to format the cell with the base colour? Not sure
what you mean?

I need four colors. I have already green, yellow, and red. I just need one
more color which would be white.

1. if it is less than 1 (100%) then green
2. if it is greater than 1 but less than 1.25 then yellow
3. if it is greater than 1.25 then red
4. if it is "NA" then white

I apply the conditional formatting to a single column that has a forumla in
each cell.

Please advise. Thanks!
Amy

 

[Gord Dibben]

I do not know which 3 conditions you have used, but assuming Green, Red and
Yellow for the first 3 conditions............

Just don't format if "NA"


Gord

Ok. But, how do I apply this "default" to a column that already has
conditional formatting set? Since the three conditions are already set, where
or how to tell the established formatting to us no color if it meets none of
these conditions?

Thanks for replying!

Gord Dibben MS Excel MVP

 

[Guest]

Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
the cell changed to its default base color, which was none. But, when I
applied the template to a new report, for some reason when "NA" was entered,
the conditional formatting applied the red color, that was meant only for
values that were above 1.25.

Hmm. Thanks for the clarification.

I do not know which 3 conditions you have used, but assuming Green, Red and
Yellow for the first 3 conditions............

Just don't format if "NA"


Gord

 

[Gord Dibben]

Happy to hear you're sorted out.

Thanks for the feedback.

Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
the cell changed to its default base color, which was none. But, when I
applied the template to a new report, for some reason when "NA" was entered,
the conditional formatting applied the red color, that was meant only for
values that were above 1.25.

Hmm. Thanks for the clarification.

Gord Dibben MS Excel MVP

 

[Guest]

To clarify, the settings are as follows

condition 1: cell value is less than or equal to then green
condition 2: cell value is between 1 and 1.25 then yellow
condition 3: cell value is greater or equal to 1.25 then red

For some reason when the cell vallue is "NA" (without the quotes) condition
3 is applied.

 

[Guest]

Actually, its not resolved. In my reply just before yours, I outlined the
setitngs that I am using. For some reason the deafult base color isn't being
applied.

A.

 

[Gord Dibben]

How about you change condition 3 to "not equal to" NA

Anything over 1.25 will show red.

Note: condition 2 colors 1.25 yellow.

Excel seems to think between 1 and 1.25 includes 1.25


Gord

To clarify, the settings are as follows

condition 1: cell value is less than or equal to then green
condition 2: cell value is between 1 and 1.25 then yellow
condition 3: cell value is greater or equal to 1.25 then red

For some reason when the cell vallue is "NA" (without the quotes) condition
3 is applied.

Gord Dibben MS Excel MVP

 

[Guest]

Didn't work.

I changed the formula to enter nothing in the cell ("0" would be misleading)
and reset the conditional formatting to

Condition 1 cell value is between .01 and 1 then green
Condition 2 cell value is between 1.01 and 1.25 then yellow
Condition 3 cell value is greater than 1.25 then red

But the empty cells are still getting red. (no pun intended!)

 

[Gord Dibben]

If the formula in the cell returns a number or "" then go with this.

1 cell value is between .01 and 1 then green
2. cell value is between 1.01 and 1.25 then yellow
3. formula is =cellref<>"" then red

Note: what color do you want if the value is less than .1

As it stands, it will be red.

I'm running out of ideas.......your parameters keep changing from one post to
the next.

Probably because I keep changing the CF entries<g>


Gord

Didn't work.

I changed the formula to enter nothing in the cell ("0" would be misleading)
and reset the conditional formatting to

Condition 1 cell value is between .01 and 1 then green
Condition 2 cell value is between 1.01 and 1.25 then yellow
Condition 3 cell value is greater than 1.25 then red

But the empty cells are still getting red. (no pun intended!)

Gord Dibben MS Excel MVP

 

[Guest]

I'll give that a try. Many thanks for your help and time, Gord.