Formatting Formula

[Rodney]

Hi Guys,

How do i write a formula to say that if, for example, cell A1 is empty it
should be coloured white?

Cheers,
Rodney

 

[T. Valko]

Assuming that the cell isn't already colored white which is the default fill
color...

Select cell A1
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box on the right:

=A1<>""

Click the Format button
Select the Patterns tab
Select the white fill color
OK out

 

[Rodney]

Hi Biff,

Thanks for that response.

However, as i have two conditional formats set up already the formula does
not seem to work on those cells to which the other conditional formats are
applied. Is there a different formula which i can use? The other two formats
simply are if a date is greater than today colour the cell green, but if
earlier than today colour the cell red.

Thanks,
Rodney

 

[T. Valko]

Ooops!

I gave you the wrong the formula!

Let's sort this out...

If you have 2 formats applied, green and red, what color is the cell to
begin with as the default?

Normally, white is the default color. If you want to set fill color with
conditional formatting you start with the a default color then as the
conditions are met the cell color changes according to those conditions.

I have a hunch that maybe your RED condition isn't set properly. Try it like
this:

=AND(COUNT(A1),A1<TODAY())

An empty cell evaluates as 0. So, if you simply test an empty cell to be
<today that condition will be true and the format will be applied. You need
to test for 2 conditions. 1) the cell does in fact contain a date (number),
and 2) the date (number) in the cell is <today. The above formula checks for
both of those conditions.

 

[Gord Dibben]

Default is "no color", not white.................just looks white


Gord