Conditional formatting and Time
Posted by Sam on January 02, 2002 3:20 PM
I have cells with conditional formatting that changes the color of cells if curent time has elapsed. Works only if I recalculate or add additional data. Is there a way that excel can automatically update without user action? I can easily add a recalculate button but was hoping that the workbook could do it every 60 seconds or so. Many thanks in advance.
Posted by Aladin Akyurek on January 02, 2002 3:30 PM
Sam --
What formula are you using, if any? Or is it a VBA thing?
Aladin
Posted by Tom Urtis on January 02, 2002 4:58 PM
Here's one way to do it
Sam,
This is possible with VBA, as follows.
Try pasting this in the worksheet's module (right click on the sheet tab, and left click on "View Code"):
Private Sub Worksheet_Activate()
Call Recalculate
End Sub
and then paste this code in a new module in the VBE (that is, press Alt+F11, click Insert > Module):
Public Sub Recalculate()
Calculate
Application.OnTime earliesttime:=Now + TimeValue("00:01:00"), _
procedure:="Recalculate"
End Sub
Return to the worksheet by pressing Alt+Q, and you should be good to go. It will update times as displayed in cells on your worksheet every minute.
Tom Urtis
Posted by Sam on January 03, 2002 1:41 PM
Aladin
This is a simple spreadsheet that looks at managing fatigue where staff on emergency callouts must not work more than 18 hours then 12 hours in the next period etc in a specified period eg.
- A2 has date and time 2 hrs before 1st period expires
- A3 is simply "Y" (notified)
Conditional formatting is as follows:
A2 =A2<now() - changes cell to red
A3 =and(A2<now,A3="Y") - returns cell to original color
Works a treat except that it only works during recalculation ie when adding more data or manually recalculating. Was hoping to have a routine that recalculated spreadsheet every minute as staff get busy during severe storms (like we are experiencing at the moment)and forget to recalculate.
Posted by Sam on January 03, 2002 1:58 PM
Re: Here's one way to do it
Thanks Tom, tried it but didn't work - I'm using NT4 and Excel 97.
Posted by Sam on January 03, 2002 2:11 PM
Re: Here's one way to do it
Don't know what happened before but works now, thanks.
