Highlight cell if the date is older than current month, also needs to be consider previous year

[uday]

Hi Experts,

I am trying to highlight cells in the A column if the value is date then only it will check for the condition where it will be highlighted if the date is older than the current month and year. I have attached the Macro workbook.

Regards,
Uday

Sub highlightDates()

Dim currentMonth As Integer
Dim currentYear As Integer
Dim cellValue As Variant

currentMonth = Month(Date) 'get current month
currentYear = Year(Date) 'get current year

For Each cell In Range("A1:A100") 'loop through cells in column A



    cellValue = cell.Value 'get cell value

    'if cell value is a date and it's older than the current month
    
    On Error Resume Next
    If IsDate(cellValue) And (Month(cellValue) < currentMonth) Or (Year(cellValue) < currentYear) Then
        cell.Interior.Color = vbYellow 'highlight cell
    End If
Next cell
  
End Sub

 

[vletm]

uday
One sample based Your file ...

 

[uday]

Thanks vletm for your prompt response. Just want to extend the condition to another level. Now, is it possible to highlight the date, if it is + one month greater than the current month's date? This means, if today's date is 03-Jan-2023 then the index of the month is 1 for Jan and now I want to highlight the date if it is 1+1, which means greater than the current month + 1 which is March 2023 and beyond.

I will be very happy for your assistance.

Thanks

 

[vletm]

uday
It should be possible.
You could check that sample code and add there Your needed feature Yourself too.

 

[uday]

I have done the changes and it's working. Thanks for your help and time.