today() conditional formatting

[Guest]

i have conditional formatting set for projects that are still pending so that

formula is =today(
formatted font is red, bol

once the project is complete, the date is manually entered into the cell (changing it from the formula =today() to an actual date (i.e.: 4/19/04))

the problem is that the conditional formatting at that point still reads the date in that cell as today's date (even though it's no longer a formula), and continues to show as red, bold font... which indicates that the project is still pending

is there a way to get around that

hope that makes sense

tia
jill

 

[Bob Phillips]

You need to include a reference to the cell itself.

Select all of the relevant cells, say starting at B2, and change the formula
to

=B2=TODAY()


--

HTH

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

i have conditional formatting set for projects that are still pending so that

formula is =today()
formatted font is red, bold

once the project is complete, the date is manually entered into the cell

(changing it from the formula =today() to an actual date (i.e.: 4/19/04)).

the problem is that the conditional formatting at that point still reads

the date in that cell as today's date (even though it's no longer a
formula), and continues to show as red, bold font... which indicates that
the project is still pending.

 

[Frank Kabel]

Hi
try the following formula
=$A$1=TODAY()
if a1 is the cell you want the conditional format apply to

 

[Guest]

that didn't seem to work... when i enter today's date in that cell, it still shows as red, bold. i know this will change tomorrow, but i'd like it to show the correct formatting for whatever date i enter, even if it happens to be today

suggestions

jill.

 

[Guest]

sorry... still not working. maybe i'm explaining poorly..

in cell A1, i enter =today() with the conditional formatting of that cell being

formula is =$A$1=today(
format cell as bold, re

so today, that cell shows '4/19/04

if the project closes today, i replace the formula '=today()' to a manual date, so i enter '4/19/04' in that cell

unfortunately, because =today() and 4/19/04 have the same value, the formatting still shows as bold, red until tomorrow

is there anything i can do about that

thanks so much for your help

jill.

 

[Bob Phillips]

You need to create a UDF like so

Function isformula(rng As Range)
isformula = rng.HasFormula
End Function

and change the formula to

=AND(isformula(A1),A1=TODAY())

--

HTH

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

sorry... still not working. maybe i'm explaining poorly...

in cell A1, i enter =today() with the conditional formatting of that cell being

formula is =$A$1=today()
format cell as bold, red

so today, that cell shows '4/19/04'

if the project closes today, i replace the formula '=today()' to a manual

date, so i enter '4/19/04' in that cell.

unfortunately, because =today() and 4/19/04 have the same value, the

formatting still shows as bold, red until tomorrow.

 

[Guest]

that worked! thanks very much for your help

jill.