468,315 Members | 1,476 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Conditional formatting and Date Difference in VBA

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.
Dec 19 '19 #1
4 1711
twinnyfo
3,650 Expert Mod 2GB
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:
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
SioSio
241 128KB
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
  13.  
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
twinnyfo
3,650 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

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