How can empty cells automatically highlight after 24 hours?
 

How can empty cells automatically highlight after 24 hours?

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 FormatConditional 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

-----Original Message-----
How can empty cells automatically highlight after 24
hours?
.

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 A2:D5

Select A2:D5

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 A2:D5
after 24 hours from the reference date/time in A1

If there are formula cells within A2:D5
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="")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"BT" wrote in message
...
How can empty cells automatically highlight after 24 hours?

The above will trigger the conditional format
for all empty* cells within A2:D5
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 A2:D5
.....

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

At least your working with the correct year!

I'm stuck in 2004! <g

Biff

-----Original Message-----
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 A2:D5

Select A2:D5

Click Format Conditional Formatting

Make the settings for Condition 1 as:
Formula Is | =3DAND(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 A2:D5
after 24 hours from the reference date/time in A1

If there are formula cells within A2:D5
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:
=3DAND(NOW()$A$1+1,A2=3D"")

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
"BT" wrote in message
...
How can empty cells automatically highlight after 24=20
hours?


.

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

-----Original Message-----
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 FormatConditional 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

-----Original Message-----
How can empty cells automatically highlight after 24
hours?
.

.

"Biff" wrote:
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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----