How to VBA code to change cells colors based on date in other cells

How to use a VBA code to change cells colors based on date in other cells

Requirement:

Looking for a VBA code:. (all 3 of my conditional formats have been used for different senarios)
Cells "AE4" to "AE2000" contains dates.

The user would like adjacent cells "AF4" to "AF2000", to change "Yellow" when "Today's" date falls within a range of 3 to 7 seven days prior to "AE" date, and "Red" if "Today's" date falls within a range of 2 days prior to 365 days after "AE" date .

Solution:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRg As Range
Dim DateDiff As Long
Dim Inter

    Set MyRg = Range("AE1:AE" & Range("AE" & 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, 1).Interior.ColorIndex = xlNone
            If ((DateDiff >= 3) And (DateDiff <= 7)) Then Target.Offset(0, 1).Interior.ColorIndex = 6
            If ((DateDiff >= -365) And (DateDiff <= 2)) Then Target.Offset(0, 1).Interior.ColorIndex = 3
        End If
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by PCI.