Conditional formatting

[sk]

I want to format cells so that they turn red if lower than a reference
number and green if higher than the number. I can do that with the format /
conditional format in the tools menu. I want to add another condition to the
cells and that is to have no formatting if the cell is empty and the
reference cell has a value. How do I do that? Thanks for your help.

 

[Frank Kabel]

Hi
one way:
change your existing conditions to the following (where A1 is the cell
to be formated and A2 the reference cell):
=AND(A1<>"",A1<A2)
and
=AND(A1<>"",A1>A2)

The cell will have a default format if none of the above conditions is
met. You may add a specific 'Blank' format with the following formula:
=AND(A1="",A2<>"")

 

[Bob Phillips]

Use a formula like

=AND(A1="",H1<>"")

where A1 is the activecell, H1 is the reference cell. Set the format as
usual.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[sk]

Thanks for your help. It worked fine.