Changes the color of a cell based on the date, Excel VBA

Steps to follow:

1. Open the VBA editor: Press Alt + F11 to open the VBA editor in Excel.
2. Create a module: In the VBA editor, go to Insert > Module to add a new module.
3. Add the code: Copy and paste the following VBA code into the module.

VBA Code

Sub ChangeCellColorBasedOnDate()
    ' Declare necessary variables
    Dim cell As Range
    Dim cellDate As Date
    Dim currentDate As Date
    ' Get the current date
    currentDate = Date   
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains a date
        If IsDate(cell.Value) Then
            ' Get the date from the cell
             cellDate = cell.Value           
            ' Compare the cell date with the current date
            If cellDate < currentDate Then
                ' If the date is in the past, color the cell red
                cell.Interior.Color = RGB(255, 0, 0)
            ElseIf cellDate = currentDate Then
                ' If the date is today, color the cell yellow
                cell.Interior.Color = RGB(255, 255, 0)
            ElseIf cellDate > currentDate Then
                ' If the date is in the future, color the cell green
                cell.Interior.Color = RGB(0, 255, 0)
            End If
        Else
            ' If the cell doesn't contain a date, do not change the color
            cell.Interior.ColorIndex = -4142 ' No color (no change)
        End If
    Next cell
End Sub

Code Explanation:

1. Declaring Variables:
   • cell: Represents each cell in the selected range (the range of cells where the color will be changed).
   • cellDate: Holds the date of the cell.
   • currentDate: Holds the current date.
2. Getting the Current Date:
   • currentDate = Date gets the current date.
3. Looping Through Each Cell:
   • For Each cell In Selection loops through each cell in the selected range.
   • If IsDate(cell.Value) checks if the cell contains a valid date.
4. Changing Cell Color Based on the Date:
   • If the cell date is less than the current date, the cell is colored red (RGB(255, 0, 0)).
   • If the cell date is equal to the current date, the cell is colored yellow (RGB(255, 255, 0)).
   • If the cell date is greater than the current date, the cell is colored green (RGB(0, 255, 0)).
5. Cells Without a Date:
   • If the cell does not contain a date, the color is reset to no fill using cell.Interior.ColorIndex = -4142.

How to Use the Code:

1. Select a range of cells containing dates in your Excel sheet.
2. Open the VBA editor (Alt + F11), then run the macro ChangeCellColorBasedOnDate.
3. The cells will automatically be colored based on their date in relation to the current date.

Customization:

• Change Colors: You can replace the RGB(255, 0, 0) for red, RGB(255, 255, 0) for yellow, and RGB(0, 255, 0) for green with other color values as needed.
• Specific Range: You can apply the macro to a specific range by modifying the code like this:

For Each cell In Range("A1:A10") ' Replace A1:A10 with your desired range