Contitional formatting help

[Jeff]

I have a problem with getting conditional formatting to work properly for
me.


What I want to do is use the cell background colour to indicate the number
of items in the cells (column)
I've spent hours trying to get it right :- (

If F2 is equal to or less than 3 (or cell is empty) 'to orange
If F2 is between 4 and 20 'to blue
If F2 is equal to or greater than 21 'to green

Solution gratefully accepted

Jeff

 

[Bearacade]

it's pretty straight forward:

Condition 1 is Cell value is less than or equal to 3 (orange)
Condition 2 is Cell Value is between 4 and 20 (blue)
Condition 3 is Cell Value is Greater than or equal to 21 (green

 

[Die_Another_Day]

Condition 1 =
Cell Value Is: less than or equal to: 3
Condition 2 =
Cell Value Is: between : 4 and 20
Condition 3 =
Cell Value Is: greater than or equal to: 21

click the format for condition 1 and choose orange
click the format for condition 2 and choose blue
click the format for condition 3 and choose green

HTH

Die_Another_Day

 

[Mark Lincoln]

In Conditional formatting, set your first condition to Greater Than or
Equal To 21 and set your color to Green.

Set your second condition to Greater Than or Equal To 4 and set the
color to Blue.

Set the last condition to Less Than 4 and set the color to Orange.

Does that work for you?

 

[Guest]

Set Custom Formulas for your conditions.

Orange Condition:
=OR(F2="",F2<=3)

Blue Condition:
=AND(F2>=4,F2<=20)

Green Condition:
=F2>=21

Note that the conditions as you described will not yield formatting for
values between 3 and 4, and between 20 and 21. Thus 3.3 and 20.7 will not be
formatted.

HTH,
Elkar

 

[Jeff]

Thanks for this.

I think my logic is a bit fuzzy. I was trying to get the blank cells to be
gray if empty or orange if value = 1 through 3 (I also mistated my origianl
question about condition 1.

Is there a way it (condition 1) can be changed to a formula so an empty cell
stays gray when cell is empty but orange if n is in the range 1 to 3?

Jeff

 

[Bearacade]

Yes, check condition 1 from less than 3 to between 1 and

 

[Jeff]

Mark,
Thanks for this. I had mis-stated my question and have restated the issue
about whether a cell is blank or between 1 and 3.

What I do see in your reply is the sequence of the condition. I would have
done it in the reverse sequence (had I know how) hehe. I have learned
something here. Thanks again.

Jeff

 

[Mark Lincoln]

Change the normal format of the cell to gray. Then follow my original
instructions for the first two conditions and change the last condition
to Greater Than 0.

To reiterate:

In Conditional Formatting, set your first condition to Greater Than or
Equal To 21 and set your color to Green.

Set your second condition to Greater Than or Equal To 4 and set the
color to Blue.

Set the last condition to *Greater Than 0* and set the color to Orange.