How to AutoUpdate as each day passes a conditional format based on date?

Question

I'm new to writing VBA code but have managed to struggle along for the past couple of weeks.

I have created a code for a worksheet change event that highlights certain dates input within particular windows like so:

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim icolor As Integer 
    Dim cell As Range 
    If Intersect(Target, Range("C3:T65")) Is Nothing Then Exit Sub 
    For Each cell In Target 
        icolor = 0 
        Select Case cell 
        Case "": icolor = 2 
        Case Is <= Date + 30: icolor = 3 
        Case Is <= Date + 60: icolor = 6 
        Case Is > Date + 60: icolor = 2 
        End Select 
        If icolor <> 0 Then cell.Interior.ColorIndex = icolor 
    Next cell 
End Sub 

It works flawlessly. However, I need the spreadsheet to basically update the highlighting each day. IE: If Date + 61 is not highlighted today, it will be highlighted tomorrow because it fits the window as Date + 60. I suspect that a simple "worksheet change event" can't do this (because it requires user input).

I have tried tweaking this into a worksheet activate code, to potentially update highlights when the document is opened (and I'm trying to avoid workbook open because I will have multiple sheets doing different things), but I can't get it to work. Any ideas on what I'm doing wrong? Or is there a better way to accomplish what I'm trying to do?

Thanks ahead of time.

Private Sub Worksheet_Activate()
    Dim icolor As Integer
    Dim cell As Range

    If Intersect(Target, Range("C3:T65")) Is Nothing Then Exit Sub
    For Each cell In Target
        icolor = 0
        Select Case cell
            Case "": icolor = 2    
            Case Is <= Date + 30: icolor = 3
            Case Is <= Date + 60: icolor = 6
            Case Is > Date + 60: icolor = 2            
        End Select
        If icolor <> 0 Then cell.Interior.ColorIndex = icolor
    Next cell
End Sub

Answer 1

There is no Target parameter passed to Worksheet_activate, so you can't use the Intersect() test. You just need to loop over your range directly

For Each cell In Me.Range("C3:T65").Cells
    'check value
Next cell 

Best approach here would be to split the hiliting logic into a separate Sub and just call that from your event handlers:

EDIT: added workbook_open

'in ThisWorkbook module
Private Sub Workbook_Open()
     Sheet1.CheckData Sheet1.Range("C3:T65")
End Sub

'in sheet code module
Private Sub Worksheet_Activate()
    CheckData Me.Range("C3:T65")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    CheckData Intersect(Target, Me.Range("C3:T65"))
End Sub

Sub CheckData(rng As Range)
    Dim icolor As Integer
    Dim cell As Range

    If rng Is Nothing Then Exit Sub

    For Each cell In rng.Cells
        icolor = 0
        Select Case cell
            Case "": icolor = 2
            Case Is <= Date + 30: icolor = 3
            Case Is <= Date + 60: icolor = 6
            Case Is > Date + 60: icolor = 2
        End Select
        If icolor <> 0 Then cell.Interior.ColorIndex = icolor
    Next cell
End Sub