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

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)


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=552488

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
Jeff wrote:
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

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?


Jeff wrote:
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

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" wrote:

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

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


"Die_Another_Day" wrote in message
ps.com...
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
Jeff wrote:
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

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


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=552488

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" wrote in message
oups.com...
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?


Jeff wrote:
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

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.

Jeff wrote:
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" wrote in message
oups.com...
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?


Jeff wrote:
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