By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,403 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,403 IT Pros & Developers. It's quick & easy.

Conditional formatting and Date Difference in VBA

P: 2
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
Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
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

100+
P: 150
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

P: 2
Thanks twinnyfo. But I expect the result through VBA.

Thanks Sio Sio. Let me try this one.
Dec 23 '19 #4

twinnyfo
Expert Mod 2.5K+
P: 3,482
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.