How to change color for data older then specified time using VBA

[nobigdeal]

Hello, I'm looking for an efficient way how to highlight data older then specified time.
In my attached example file I have two tables with data and two tables with a timestamp data(one timestamp table for each table with data).
Please note, there is always one blank cell between cells with data.
What I would like to achieve is run a macro every minute and highlight cells(red background) with data older then 30 minutes.

Please see atteched file.
https://ufile.io/ed2nw

Thank you for any help.

 

[nobigdeal]

Any suggestions?

 

[John_w]

Try this macro:

Public Sub Update_Data_Table_Colours()

    Application.ScreenUpdating = False
    With ActiveSheet
        Fill_Data_Table_Cells .Range("A4:BM20"), .Range("A49:BM65"), RGB(255, 230, 153)
        Fill_Data_Table_Cells .Range("A26:BM42"), .Range("A73:BM89"), RGB(198, 224, 180)
    End With
    Application.ScreenUpdating = True
    
End Sub


Private Sub Fill_Data_Table_Cells(dataTable As Range, timestampTable As Range, defaultFillColour As Long)

    Dim r As Long, c As Long
    Dim fillColour As Long
    
    For r = 1 To timestampTable.Rows.Count
        For c = 1 To timestampTable.Columns.Count Step 2
            fillColour = defaultFillColour
            If IsDate(timestampTable.Item(r, c).Value) Then
                If Now > timestampTable.Item(r, c).Value + TimeValue("00:30:00") Then
                    fillColour = RGB(255, 0, 0) 'red
                End If
            End If
            With dataTable.Item(r, c).Interior
                .Pattern = xlSolid
                .Color = fillColour
            End With
        Next
    Next

End Sub

Have a look at Application.OnTime if you want to run the macro automatically every minute.