How to use a VBA code to change cells colors based on date in other cells
Category: [Excel] Demo Available
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.
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.