conditional format

  • Thread starter Thread starter L.Peter
  • Start date Start date
L

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
 
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
 
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.
 
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
 
Back
Top