Live time stamp conditional formatting - Excel

Question

I need to make a comparison between a cell with a "live" time stamp and another cell with a static time. I am running a macro which updates the cell time every 60 seconds.

If the actual time is more than 40mins past the static time I would like to conditionally format a cell to change colour.

Thanks for your help.

Answer 1

Instead of running a macro, you could use the '=now()' function in the cell containing the current time/date, then have another cell displaying the difference between the static time cell and the current time cell.

Then, if your cell containing the time difference is a1:

If Cells(1, 1) > 40 Then
    Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If

Or whatever color you want in the flagged time difference cell.

Answer 2

Assuming that your static time (formatted as time) is in A3 and your "live" time is in B3, First note that B3-A3 will give you the difference of times. Difference of time is the value of percentage of day. So Lets say A3 is 11:18 AM and B3 = 12:00 Noon B3-A3 = 0.031944, multiplied by 1440 (minutes in a day) gives you 46 min.

SO apply conditional formatting as follows

1. Select rule to say format based on a formula
2. =(B3-A3)*1440>40
3. Choose format
4. Applies to the cell you want like $b$3