470,575 Members | 1,607 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

Conditional formatting and Date Difference in VBA


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.
Dec 19 '19 #1
4 1946
3,653 Expert Mod 2GB

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:
Expand|Select|Wrap|Line Numbers
  1. =A1<=TODAY()
  2. =A1<=TODAY()+10
  3. =A1>TODAY()+10
Just set your colors as desired in the Conditional Formatting Editor.

Hope this hepps!
Dec 19 '19 #2
269 256MB
When the target date is listed in the column after A2
Expand|Select|Wrap|Line Numbers
  1. Dim i As Integer
  2. Dim MaxRow As Integer
  3. MaxRow = Range("A1").End(xlDown).Row
  4. For i = 2 To MaxRow
  5.     If DateDiff("d", Now, Cells(i, 1).Value) < 0 Then
  6.         Cells(i, 1).Font.Color = RGB(255, 0, 0)
  7.     ElseIf DateDiff("d", Now, Cells(i, 1).Value) = 10 Then
  8.         Cells(i, 1).Font.Color = RGB(234, 147, 10)
  9.     ElseIf DateDiff("d", Now, Cells(i, 1).Value) >= 10 Then
  10.         Cells(i, 1).Font.Color = RGB(0, 128, 0)
  11.     End If
  12. Next i
Dec 23 '19 #3
Thanks twinnyfo. But I expect the result through VBA.

Thanks Sio Sio. Let me try this one.
Dec 23 '19 #4
3,653 Expert Mod 2GB
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.
Dec 23 '19 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.