Conditional Formatting Question

[Matt]

I have a 3 condition format on a cell. They are as follows.

Condtion 1: =And(A1 > 0, A1 < 1) If true, format to color green.

Condtion 2: =And(A1 > 1, A1 < 1.99) If true, format to color yellow.

Condtion 3: =(A1 > 1.99) If true, format to color red.

All three conditions work fine when a value is entered into A1 however when
the cell is blank, the font defaults to red. I want it to have no color when
no value is entered.

Does anyone know how to do this? Any help is greatly appreciated.

Thanks,

Matt.

 

[Dave Peterson]

You get a red fill when the cell is empty--no space, no formula, no nothing???

I don't in xl2003.

Are you "clearing" the cell by using a space character? If you are, stop it
<vbg>. It'll cause more problems down the road.

But you could modify your formula:
=AND(ISNUMBER(A1),(A1>1.99))

 

[T. Valko]

when the cell is blank, the font defaults to red.

Hmmm...

When the cell is blank how can you see red text?

This condition will allow a TEXT entry to display in red, but that means the
cell is not blank.

Condtion 3: =(A1 > 1.99) If true, format to color red.

Use:

=AND(ISNUMBER(A1),A1>1.99)

 

[Ian]

I tried this and the font was black (default colour) when A1 is blank.

Set the font colour in your cell to match the background colour.

Also, from your conditions, what should happen if A1=1 or A1= 1.995 (ie the
"holes" in the sequence)?

I'm guessing what you are looking for is:
condition 1 has a value but less than 1
condition 2 is 1 or more, but less than 2
condition 2 is 2 or more

If so your formulae should be for conditions 2 & 3 should be
=And(A1 >=1, A1 < 1.99) and
=(A1 >=2)