Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can empty cells automatically highlight after 24 hours?
How can empty cells automatically highlight after 24 hours?
|
#2
|
|||
|
|||
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? . |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 ---- |
#5
|
|||
|
|||
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? . |
#6
|
|||
|
|||
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? . . |
#7
|
|||
|
|||
"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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make empty cells as zero in excel add-ins for SQL Server an | Excel Worksheet Functions | |||
CountIF cells are not empty | Excel Discussion (Misc queries) | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Empty Cells, Spaces, Cond Format? | Excel Discussion (Misc queries) | |||
Protected cells -automatically format to a different color | Excel Discussion (Misc queries) |