Maruf Islam
Typical Excel datasets contain hundreds of rows and tens of columns. As a result, users often use Excel VBA compare dates to today to highlight or filter out data. Versions of the VBA FOR loop entwined with VBA IF and VBA custom function can be used to compare dates against the current date.
Let’s say we have a sales dataset containing the amounts of sales of each date. We want to compare the dates to the current date using Excel VBA.
In this article, we demonstrate multiple macro variants using VBA FOR and VBA IF functions to compare dates to today.
Download Excel Workbook
3 Easy Ways to Compare Dates to Today with Excel VBA
Excel macros require Module insertion prior to their execution. Follow the below instructions to be able to insert a Module:
➤ Press ALT+F11 simultaneously to open the Microsoft Visual Basic window.
➤ Go to Insert > Module. Afterwards, Excel opens a VBA Module where users can insert their macros.
Go through the latter section to find out the appropriate macro variants suitable for your case.
Method 1: Highlighting Cells to Compare Dates to Today Using Excel VBA
From the hundred rows, users can highlight the current date and also the days before or after the current date using Excel VBA. Excel’s VBA FOR and VBA IF functions enable users to do so.
Step 1: Type 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 Explanation
- Firstly, the macro calls the Sub using Sub Highlight_Dates_Compare_Today.
Sub Hightlight_Dates_Compare_Today()- Secondly, the macro defines the variable types as Integer.
Dim m As Integer- Thirdly, macro executes a VBA FOR
- Then the VBA IF function iterates the cells to check the imposed condition using the VBA Date. The VBA DATE function returns the current date.
- .Font.Color command colors the cells that match the current date.
- Users can modify the macro to highlight the days before or after the current date.
Before Days
If Cells(m, 2).Value < Date Then Cells(m, 2).Font.Color = vbRedAfter Days
If Cells(m, 2).Value > Date Then Cells(m, 2).Font.Color = vbRed
Step 2: After inserting the macro into the module, use the F5 key or go to Run > Run Macro F5 to execute it. Then return to the worksheet to check whether the macro highlights the matched cell or not, as shown in the latter image.
Read More: Conditional Formatting for Dates Older Than Certain Date in Excel
Similar Readings
- Excel Formula If Date Is Greater Than 2 Years (3 Examples)
- If Cell Contains Date Then Return Value in Excel (5 Examples)
- Conditional Formatting Based on Date Older Than 1 Year in Excel
- Excel Conditional Formatting for Date Within 3 Months (3 Methods)
Method 2: Coloring Rows to Compare Dates with Excel VBA
As an alternative to Method 1, Excel Macros can color the entire rows upon comparing them to a specific column value.
Step 1: Type the below macro in the 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 Explanation
- At first, the macro calls the Sub using Sub Coloring_Dates_Against_Today. Â
Sub Coloring_Dates_Against_Today()- Then the macro declares the variables as Long and String.
Dim mLastRow As Long, mRow As Long
Dim mColumn As String- Also, set the mColumn variable to B Column.
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 imposed conditions.
- .Rows.Interior.ColoeIndex colors the matched rows with the assigned color index (i.e., 6 (Yellow)).
- Users can modify the macro to highlight the days before or after the current date.
Before Days
If IsDate(.Cells(mRow, mColumn).Value) And .Cells(mRow, mColumn).Value < DateAfter Days
If IsDate(.Cells(mRow, mColumn).Value) And .Cells(mRow, mColumn).Value > Date
Step 2: Run the macro, then go back to the worksheet. You see, Excel colors the entire rows.
Read More: Excel Formula If One Date is Greater Than Another Date
Method 3: Using VBA Custom Function to Compare Dates to Today
What if users want to know the dates’ status that fall within the current month and year? A custom function can display TRUE or FALSE to indicate the dates matched the current month and year’s status in the adjacent cells.
The TODAY() function returns today’s date in the C3 cell.
Step 1: Type the following macro in the 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 Explanation
- At first, the macro named the function as MnthYr. And assign cell references to Date.   Â
Function MnthYr(mDate As Date) As Boolean- Initially the function gets assigned to False.
MnthYr = False- The VBA IF function fixes the C3 cell as the current date and extracts the month and year using the VBA Month and Year functions respectively.
- 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.
- Users can modify the macro to highlight the previous or following month and year of the current date.
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: After inserting the macro into the module, go to the worksheet. Type the following formula into cell D6.
=MnthYr(B6)
Step 3: Drag the Fill Handle to display True or False in the adjacent cells.
🔺 Users can use Conditional Formatting (Home > Conditional Formatting > Highlight Cells Rules > Equal To) to color format function outcomes as depicted below.
Read More: Excel Formula If Date Is Less Than Today (4 Examples)
Conclusion
This article demonstrates multiple variants of Macros to Excel VBA comparing dates to today. Go through the Macro Explanations for a better understanding. Use any of the methods to accomplish your goal. Comment if you have further inquiries or have anything to add.
Do check out our awesome website, Exceldemy, to find interesting articles on Excel.
