Conditional formatting, "0" not equal to blank

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Johan - 24 Apr 2007 13:33 GMT

I would like the following conditional formatting

A blank cell = no filling

0 to 0.99 = red
1 to 1.99 = yellow
more than 2 = green

The problem I have is that blank cells are red. I just want the cells
where i type in "0" to be red.

Is it possible to solve this problem?

thanks!

Reply to this Message

Bob Phillips - 24 Apr 2007 13:49 GMT

Try a formula of

=AND(A1=0,LEN(A1)>0)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I would like the following conditional formatting
>
[quoted text clipped - 10 lines]
>
> thanks!

Reply to this Message

Earl Kiosterud - 24 Apr 2007 14:00 GMT

Johan,

Select the cells. Note which is the active (white) cell). In the first condition, use
"Formula is:"
=C1>=2 Set it to green

Same for the second condition:
=C1>=1 set to yellow

Third:
=AND(ISNUMBER(C1),C1>=0) set to red

Change the C1 to whatever your active cell is. You may want to use = in place of the >=,
depending on where you want to change to take place.

Signature

Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already
top-posted, the thread gets messy. When in Rome.

-----------------------------------------------------------------------

>I would like the following conditional formatting
>
[quoted text clipped - 10 lines]
>
> thanks!

Reply to this Message

Harlan Grove - 24 Apr 2007 16:41 GMT

Johan <johan8...@hotmail.com> wrote...
...

>The problem I have is that blank cells are red. I just want the cells
>where i type in "0" to be red.
>
>Is it possible to solve this problem?

Blank cells are evaluated as numeric zeros, so you can't test for <>
0. You have to test that the cell contains a number, so you can't use
the simple condition that the cell value is between 0 and whatever.
You have to use a FORMULA condition like

=AND(COUNT(x),x>=0,x<1)

where x is a placeholder for your cell address.

Reply to this Message

Rate this thread: