Conditional formatting and Date Difference in VBA

[abhivishak]

Hi,

I am creating a macro in Excel to extract some information from Master excel.

In Master sheet, have some data with the column- 'Target dates' (list of Dates) in it.

I want to compare Target dates with the current date and if the Target dates already breached the current date, then should be highlighted in red.

If Target date has 10 days from the current date, then should be highlighted in amber.

If Target date has more than 10 days from the current date, then should be highlighted in green.

Please help me with the codes.

[twinnyfo]

abhivishak,

Welcome to Bytes!

I moved your thread to the appropriate forum.

Assuming your Target Date is in Column A, your formulas within Conditional Formatting (for respective Red, Yellow and Green) would look like this:

=A1<=TODAY()
=A1<=TODAY()+10
=A1>TODAY()+10

Just set your colors as desired in the Conditional Formatting Editor.

Hope this hepps!

[SioSio]

When the target date is listed in the column after A2

Dim i As Integer
Dim MaxRow As Integer
MaxRow = Range("A1").End(xlDown).Row
For i = 2 To MaxRow
    If DateDiff("d", Now, Cells(i, 1).Value) < 0 Then
        Cells(i, 1).Font.Color = RGB(255, 0, 0)
    ElseIf DateDiff("d", Now, Cells(i, 1).Value) = 10 Then
        Cells(i, 1).Font.Color = RGB(234, 147, 10)
    ElseIf DateDiff("d", Now, Cells(i, 1).Value) >= 10 Then
        Cells(i, 1).Font.Color = RGB(0, 128, 0)
    End If
Next i
 

[abhivishak]

Thanks twinnyfo. But I expect the result through VBA.

Thanks Sio Sio. Let me try this one.

[twinnyfo]

Is there a “requirement” to do something in VBA? It seems to be the more difficult route when conditional formatting is built into Excel.

Unless, of course, I am missing something about your project.