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

< Back to Search results

 Category: [Excel]  Demo Available 

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




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 .




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.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use VBA code to check interactions in the formula bar
How to use a VBA code for clipart


How to create VBA to place current month and year in a cell
How to use Excel VBA macro to convert multiple columns to multiple rows


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)