conditional format

[L.Peter]

Dear Group,
how can i set the background color if a cell in a column if the follwing
conditions are true:
if the cell value > today() and the cell is not empty: change color to
red

at the moment, i can set it change color when cell value >today() but the
problem is some time the cell value is " ", and because cell value has
some white space so the color is changed (which is not desire), how can i
check something like:
if (len(trim(cel value))>0,red color, white color)

TIA

L.Peter

 

[Trevor Shuttleworth]

L.Peter

=AND(F5>TODAY(),F5<>"")

Regards

Trevor

 

[Frank Kabel]

Hi
first you may try to get rid of these 'pseudo-blank' cells. Is there a
reason for a value of " "?. But if you have to uuse these kind of
values you may try the following formula in the conditional format
dialog ('Format - Conditional format')
=AND(A1>TODAY(),LEN(TRIM(A1))=0)

and choose your format for this condition

 

[Norman Harker]

Hi L.Peter

Try:

=AND(A1>TODAY(),ISTEXT(A1)=FALSE)


This covers
A1 is "" (Often used in formulas)
A1 is ' (Some darn fool's been clearing with the space bar again!)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[L.Peter]

Dear Trevor,
thank you for the quick reply,
based on what you suggested, this works :=AND(G15>TODAY(),LEN(TRIM(G15))<>0)

Thank you very much

Peter