472,982 Members | 2,293 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,982 software developers and data experts.

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 2845
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
272 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

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

Similar topics

by: moon | last post by:
Hi all, I want to write an expression that would color code a set of dates. I have two fields in a continuous form called "beginning date" and "end date" . I want to change the color of a record...
by: Jouke Langhout | last post by:
Hello all! For quite some time now, I've got the following problem: Access won't close properly when a user closes the application. An ACCESS process stays active and that process can only be...
by: Sara | last post by:
The problem: Conditional formatting bold, red when field Value < date() sets the field background to white - always - whether condition is met or not. I want the field unfilled and just red/bold...
by: jodyblau | last post by:
I am trying to do some conditional formatting with a textbox and I am not able to quite get it. Here is the setup: My record includes a textbox called and two checkboxes the first called ...
by: OdAwG | last post by:
Hello Again Access GURU's, Need some help with Conditional Formatting and datediff. What I am trying to do is the following: I have three text boxes with dates in them and what I want to do...
by: Typehigh | last post by:
I have many text fields with conditional formatting applied, specifically when the condition is "Field Has Focus". Without any events associated with the fields the conditional formatting works...
by: ApexData | last post by:
In a Continuous form, I want to use Conditional Formatting to change the background color of a date field based on a condition. 1-The color chart in the CF menu option offers limited colors. How...
by: keri | last post by:
I would like to set up conditional formatting on a form. One of the fields on the form is a date (). If the calldate field is over 365 days ago I would like all the other fields on the form line...
by: afromanam | last post by:
Regards, Please help What I'm trying to do is this: (and I can't use reports since I must export to Excel) I export some queries to different tabs in an excel workbook I then loop through...
by: fredmart | last post by:
Hi, I'm having problems finding a way to apply conditional formatting based on comparing the text contents of two fields in two different tables. What I'd like to do: I've got two tables that...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.