How can empty cells automatically highlight after 24 hours?

[BT]

How can empty cells automatically highlight after 24 hours?

 

[Biff]

Hi!

Here's one way but it isn't as automatic as you're
thinking.

First you have to have a starting time to base a formula
on. You could enter a date-time stamp in a cell. Say for
example that cell is A1. You enter:

1/17/2004 2:00 PM

Now select the EMPTY cells that you want to highlight.
Goto Format>Conditional Formatting.
From the dropdown select Formula IS.
Enter this formula in the box:

=NOW()>$A$1+1

Click the Format button and select the style(s) that you
want then OK out.

Now the caveat: This will not automatically change the
highlight at EXACTLY the time intended because the formula
is NOT DIRECTLY LINKED TO YOUR SYSTEM CLOCK.

The NOW() function is volatile, meaning it will update
EACH time there is a workbook calculation. So, on the
first calculation after 24 hours have passed the empty
cells will highlight.

To do EXACTY what you want would require VBA.

Biff

 

[Max]

Just some thoughts ..

Assume we have input a "reference" date and time
in A1: 16-01-2005 3:16:22 PM (example)

And our working range is say, in A25

Select A25

Click Format > Conditional Formatting

Make the settings for Condition 1 as:
Formula Is | =AND(NOW()>$A$1+1,ISBLANK(A2))
Click Format button > Patterns tab > Light brown? > OK

Click OK at the main dialog

The above will trigger the conditional format
for all empty* cells within A25
after 24 hours from the reference date/time in A1

If there are formula cells within A25
which could evaluate to ""
and you want these cells to be treated like "empty" cells
if the formula returns are "",
just change the conditional format formula above to:
=AND(NOW()>$A$1+1,A2="")

 

[Max]

The above will trigger the conditional format

for all empty* cells within A25
after 24 hours from the reference date/time in A1

The asterisk for "empty" is referring to the ensuing para
i.e. the 1st line in the following para should read as:

*If there are formula cells within A25

.....

 

[Biff]

At least your working with the correct year!

I'm stuck in 2004! <g>

Biff

 

[Biff]

Hmmm....

I seem to have forgotten the most important aspect of
this.

Testing the cells for a blank (empty) condition.

See Max's post. It's pretty much the same as mine.

Biff

 

[Max]

At least your working with the correct year!
I'm stuck in 2004! <g>

Aha, gotcha ! ... then again sometimes it's nice to be nostalgic <g>