How to Compare Dates to Today with Excel VBA

The following sales dataset contains the amounts of sales and the date. To compare the dates to the current date:

Press ALT+F11 to open the Microsoft Visual Basic window.
Go to Insert > Module. Excel opens a VBA Module.

Method 1 – Highlighting Cells to Compare Dates to Today Using Excel VBA

Step 1:

Enter the following macro in the inserted Module.

Sub Hightlight_Dates_Compare_Today()
Dim m As Integer
For m = 4 To 15
If Cells(m, 2).Value = Date Then Cells(m, 2).Font.Color = vbRed
Next m
End Sub

Macro Breakdown

calls the Sub using Sub Highlight_Dates_Compare_Today.

Sub Hightlight_Dates_Compare_Today()

defines the variable types as Integer.

Dim m As Integer

executes a VBA FOR
the VBA IF function iterates the cells to check the condition using the VBA Date. The VBA DATE function returns the current date.
.Font.Color command colors the cells that match the current date.

The macro can be modified to highlight the days before or after the current date.

Days Before

If Cells(m, 2).Value < Date Then Cells(m, 2).Font.Color = vbRed

Days After

If Cells(m, 2).Value > Date Then Cells(m, 2).Font.Color = vbRed

Step 2:

Press F5 click Run.
Go back to the worksheet and see the result.

Method 2 – Coloring Rows to Compare Dates with Excel VBA

Step 1:

Enter the following macro in the inserted Module.

Sub Coloring_Dates_Against_Today()
Dim mLastRow As Long, mRow As Long
Dim mColumn As String
mColumn = "B"
 With ActiveSheet
 mLastRow = .Cells(.Rows.Count, mColumn).End(xlUp).Row
  For mRow = 4 To mLastRow
    If IsDate(.Cells(mRow, mColumn).Value) And .Cells(mRow, mColumn).Value = Date Then
     .Rows(mRow).Interior.ColorIndex = 6
    End If
  Next mRow
 End With
End Sub

Macro Breakdown

calls the Sub using Sub Coloring_Dates_Against_Today.

Sub Coloring_Dates_Against_Today()

declares the variables as Long and String.

Dim mLastRow As Long, mRow As Long
Dim mColumn As String

sets the mColumn variable to Column B.

mColumn = "B"

VBA WITH statement performs VBA FOR and VBA IF functions on a single object.
VBA FOR assigns the row numbers and VBA IF executes the conditions.
.Rows.Interior.ColoeIndex colors the matched rows with the assigned color index ( 6 (Yellow)).

The macro can be modified to highlight the days before or after the current date.

Days Before

If IsDate(.Cells(mRow, mColumn).Value) And .Cells(mRow, mColumn).Value < Date

Days After

If IsDate(.Cells(mRow, mColumn).Value) And .Cells(mRow, mColumn).Value > Date

Step 2:

Press F5 click Run.
Go back to the worksheet and see the result.

Method 3 – Using a VBA Custom Function to Compare Dates to Today

Step 1:

Enter the following macro in the inserted Module.

Function MnthYr(mDate As Date) As Boolean
MnthYr = False
If Not IsDate(Cells(3, 3)) Then Exit Function
If Month(mDate) = Month(Cells(3, 3)) And Year(mDate) = Year(Cells(3, 3)) Then
MnthYr = True
End If
End Function

Macro Breakdown

the function is named as MnthYr and cell references are assigned to Date.

Function MnthYr(mDate As Date) As Boolean

the function is assigned to False.

MnthYr = False

The VBA IF function sets C3 cell as the current date and extracts the month and year using the VBA Month and Year functions.
The VBA AND function combines both conditions into one.

If Month(mDate) = Month(Cells(3, 3)) And Year(mDate) = Year(Cells(3, 3))

The macro returns True upon matching the month and year with the current date.

The macro can be modified to highlight the previous or following month and year.

Previous Months and Years

If Month(mDate) < Month(Cells(3, 3)) And Year(mDate) < Year(Cells(3, 3))

Following Months and Years

If Month(mDate) > Month(Cells(3, 3)) And Year(mDate) > Year(Cells(3, 3))

Step 2:

Press F5 click Run.
Go back to the worksheet.
Enter the following formula in D6.

=MnthYr(B6)

Step 3:

Drag the Fill Handle to display True or False in the adjacent cells.

Use Conditional Formatting (Home > Conditional Formatting > Highlight Cells Rules > Equal To) to format the function outcomes as shown below.