What I'm trying to is when the sheet activates to run a check on a range E2:E(last cell). So if the date is past a certain time frame (3 to 7 days) color background yellow, and if the date is past 7 to 365 days color back ground red. the Vb code i have works but only if you enter a date in the cell and change it contents. so here is what i have so far. please help my brain is fried. 


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim MyRg As Range
    Dim DateDiff As Long
    Dim Inter
    
    Set MyRg = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row)
    If Intersect(Target, MyRg) Is Nothing Then Exit Sub
    If (IsDate(Target)) Then
        DateDiff = Int(Date - Target.Value)
        Target.Offset(0, 0).Interior.ColorIndex = xlNone
        If ((DateDiff >= 3) And (DateDiff <= 7)) Then Target.Offset(0, 0).Interior.ColorIndex = 6
        If ((DateDiff >= 7) And (DateDiff <= 365)) Then Target.Offset(0, 0).Interior.ColorIndex = 3
    End If
End Sub
Thank you

V/R
Patrick