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
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

[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?


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

[Elkar]

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
>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Bearacade]

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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >