Apply Color Coding to Dates based on conditioning

**DarrenH** · 08-01-2007, 11:32 PM

Hi All,

Im working on a large workbook with multiple worksheets. Each of these
worksheets has a large amount of data relating to training records. In
particular, the date that an employees certification is due to expire
(eg: 01/02/2007 or 31/09/2009 etc)...(im also in Australia so my date formats may be different).

I need to color code these records with the below logic, eg:

If Cell Date is <Today() = Red Text or Cell
If Cell Date is <18months from Today()+1 = Yellow Text or Cell
If Cell Date is >18months from Today() = No change - leave white

I have basic VBA knowledge, but will be able to work my way through
some of the code that is posted.

It's also key to note that there are multiple 'Blank' and 'Text' Cells
in these worksheets. I only want to apply this code to a cell if it is
populated with a date, is this possible ?? (eg: i only want to change
the color of the dates listed, not the other cells with text and single
numbers).

I have played around with the Conditional Formatting but i will need
more than 3 conditions in the future. I've also attached a small
screenshot of the data for your convenience.

Thanks in advance for all of your help with this post.

Cheers,

Darren

**rylo** · 08-02-2007, 01:04 AM

Darren

see if this gets you started. It is an event macro for the relevant sheet. Adjust the ranges as required.

Please Login or Register  to view this content.

rylo

**boylejob** · 08-02-2007, 01:06 AM

Darren,

Maybe something like this would work. You will have to make some adjustments so it will match the way your worksheet is set up, but hopefully this will get you started.

Please Login or Register  to view this content.

**DarrenH** · 08-02-2007, 02:24 AM

Rylo and Boylejob - cheers for your help...

I kinda think i may be out of my depth tho...

Rylo - when i paste your macro into VBA, it fails and comes up with a message asking what Macro i want to run...(im presuming thats because there's no Macro Name)...so i gave it a name...'Sub abc()'...but then i get another error with the "Worksheet_Change(ByVal Target As Range)" text...I've tried to move this down a line, and even tried to put 'Dim' in front of it but then it just produces another error and highlights the 'ByVal' text...it was at this point that i moved onto Boylejob's reply...

Boylejob - I altered the code a little (changed the "For lRow = 1 To 10 Step 1" text to 30 instead of 10...just to cater for the amount of columns in my spreadsheet)...i've also named the specific worksheet to match the name of a sheet in my workbook...but when i run the code it stops at the "If CDate(wsSht1.Cells(lRow, 1)) < Date Then"...when i hover the cursor over the highlighted text it says "CDate(wsSht1.Cells(lRow, 1)) = <Type mismatch>"...(is the Type Mismatch the problem, and what exactly does this mean ??)

Sorry all, but im new to VBA and this is my first post on this forum...if there's a better way for me to display this information, i'd be happy to oblige...

Thanks again for your help...

Cheers - Darren

**boylejob** · 08-02-2007, 12:39 PM

Darren,

I realize you said you were working with a large workbook, but is there anyway you can create a sample with a good representation of the data you are working with? Then zip it and post it. This almost always helps as it allows us to work with the actual data you are working with.

I just created a small test of what I imagined you have which may actually be totally different. I'll be more than happy to keep working on this with you until we come up with a solution.

One other thought. Looking at this line of code

Please Login or Register  to view this content.

It is looking in Column 1 (which is also Column A) for your date. Is your date in Column A? If the item in Column A is not a date that would cause you to have a type mismatch. If Column A is not where your date is, you need the change the 1 to whatever column the date is appearing in.

If the date is not in Col A, you will also need to change the 1 in the lines that look like this.

Please Login or Register  to view this content.

Since you are in Australia we are probably going to have about a 12 hour time difference. My responses may not seem to come real quick. Your reply was about 2 in the morning where I am at.

**rylo** · 08-02-2007, 06:52 PM

Darren

My code was a sheet event macro.

Go to the sheet you want this to action, right click on the tab, select view code, then paste the macro. Make sure you remove it from any general module.

You will have to adjust the ranges you want this to work on. At the moment, it is only set for A3 and A4.

rylo

**DarrenH** · 08-02-2007, 07:31 PM

Thanks Jeff...and yeah, there are no dates in Column A...The dates always start in Column D...So would it work if you could loop the code to run from Column D to Column Z ?? (NOTE: at this stage my data would not exceed Row 500)...

I've created an example of the data im working with, given that it's confidential to our business, i've replace some data with fictional information...(all information in Italics has been changed)...

Currently i have 26 Sheets in the workbook, each one relates to a specific Depot (you notice i've classified it as 'Depot 1' in the example attached)...each Depot will be set out similar but will have alot more information and records to analyse...

I completed my VBA Basic's Training about 3 months ago but like all courses, it was not tailored to my specific needs, so there's been alot of trial and error, research on the net and frustration prior to using the forums...so im extremely appreciative of your help

It's only 9.30am here (Friday Morning) so if you dont get this in the next 6-7 hours i wont be back in til Monday...but like i said, i dont have a deadline with this so please only action this in your spare time...

Cheers - Darren

**DarrenH** · 08-02-2007, 07:53 PM

Hey Rylo...i've made the relevant range changes and have pasted it into the specific sheet as stated but it continues to bring up the 'Macro' dialog box, wanting to know what Macro to run ??

It's ok, i've given Jeff a fair bit of info to work through but thanks for your assistance...

One question tho...you mentioned a 'Sheet Event Macro'...is this something completely different to the normal module macros i am familiar with (my understanding is in the code you write you stipulate what Sheet to action, as opposed to having a specific Sheet Marco ?? This may have been something we didnt cover in my training)...

Thanks again for your help...

Cheers - Darren

**boylejob** · 08-02-2007, 08:11 PM

Darren,

I took your example and made the code to fit. Give this a try and see what happens. In the example most dates remailed white based on the logic.

You will need to adapt the code to run with your larger project. I would suggest you play around with this in your example. Change the dates, run and rerun. You may even want to mess around with the logic and change it to see what happens.

Anyway, let me know how it goes.

**DarrenH** · 08-02-2007, 08:57 PM

Jeff,

I've managed to get your code to color some of the cells (thanks) but there's a few tweaks that need to be made...

Im also not sure that it's analysing the data correctly...when i run it against another sheet (and change the perametres) it doesnt seem to apply the correct color coding...eg: if you work off the logic of 'Yellow' = data is between today and 18 months in the future (or Today() + 548 days) then you would presum that it would highlight anything up until the start of Feb 2009...but there are some cells that are highlighted yellow with a date of 01/03/2009 (possible American/Australian date discrepancies ??)...i know here in Aus our date format is DD/MM/YYYY and i believe in America it's MM/DD/YYYY...

Im also getting an error with the below code when i try and run it against some sheets...(or a sheet i've already run it against but manually formatted back to 'No Fill')...

If CDate(sDateTemp) < Date Then (comes up with an error of sDateTemp = "/" when i hover my mouse over the code)

Also, as each sheet is different, the range will always change...so i cant use the below code unless i change it for every sheet, which will prove to be very tedious:

'Currently looks at rows 15 thru 55
For lRow = 4 To 55 Step 1

'Currently loops thru columns D thru W (4 thru 23)
For lCol = 4 To 23 Step 1

As a thought...Can it be set up to search a worksheet from A1:Z500 for a cell with a "/" in it(the only cells with an "/" in it would be a date cell)...if found, analyse the cell against the date conditions (color red/yellow) and color appropriately ??

I know what needs to happen but i dont have enough knowledge of VBA yet to make it happen...hopefully my explanation makes sense...

Sorry to hit you with all of these problems Jeff...

Cheers - Darren

**boylejob** · 08-02-2007, 11:10 PM

Darren,

Don't worry about hitting me with problems. This is how you learn and I'm more than happy to help. I am by no means an expert and I don't program in VBA for a living. I have been working in VBA for around 8 years now as a means of making my job easier and because I really like to program. I say all that to let you know I may not have the answers to all problems.

Im also not sure that it's analysing the data correctly...when i run it against another sheet (and change the perametres) it doesnt seem to apply the correct color coding...eg: if you work off the logic of 'Yellow' = data is between today and 18 months in the future (or Today() + 548 days) then you would presum that it would highlight anything up until the start of Feb 2009...but there are some cells that are highlighted yellow with a date of 01/03/2009 (possible American/Australian date discrepancies ??)...i know here in Aus our date format is DD/MM/YYYY and i believe in America it's MM/DD/YYYY...

Lets use a date of August 2, 2007. If I add 18 months and one day to that date I get February 3, 2009 and if I add 548 days I get January 31, 2009. When I enter 01/03/09 (March 01, 2009) on the sample sheet it does not get highlighted. I am not sure what exactly is happening here. I believe I have read that non American date formats can be a challenge. Basically, this bit of code

Please Login or Register  to view this content.

should be converting the date. I have modified this a little so 01/03/09 and 01/03/2009 yield the same thing.

I have made a few modifications to the code that should get you closer to what you are wanting to do and I have attached the sample file again. It will now look at each cell from A1:Z500. It will look to see if the 3rd character in the cell is "/" and if it is, it will evaluate it as a date. I use the 3rd character to account for things like "Depot 1 - Tutors/Drivers". If you just search for a "/" outright there are some things that are not dates that will be evaluated and cause problems.

See if these new adjustments get you any closer to where you are wanting to be. Play around with the dates and if it is still not working let me know. If you can make the problem happen in the sample, let me know exactly what cell you are looking at that has the problem. This might help me to pinpoint what is going on.

If we cannot figure out the problem, we'll see if we can get some other folks to look at it and see what they think.

**rylo** · 08-03-2007, 12:16 AM

Darren

I've taken one of the example file, made a copy of depot 1, added the event macro and "run" for the range D15:W36.

I've also taken the last macro from Jeff and run it across the sheet Depot 1

There are some differences in the results.

This should show you the placement / action of an event macro. If you take depot 1 (2), and change the D15 to 13/2/07, you will see that it is actioned when you press enter.

HTH

rylo

**DarrenH** · 08-03-2007, 01:39 AM

Ok...the Code is working and picking up the dates that need to be analysed based on the 3rd "/" in any cell (its perfect!!)...it's also color coding where appropriate (98% of the time)...but i have still noticed some example of dates being colored where they shouldnt (and it's not making sense ??)

It's got me baffled because logic would say that 01/03/2009 should not be highlighted yellow...I've attached a data dump of one of the depots...the code has already been run so you'll see the colors are already in place...have a look at Row 9, it has a date of 01/03/2009, why is this Yellow ?? Row 61 has a date of 26/02/2009 and Row 86 has a date of 08/02/2009 (both before 01/03/2009) and they are still White ?? It's just odd that it's applying the Yellow to 01/03/2009 (this is what initially sparked my Aus V America Date format query but i've applied your other piece of code and it's still doing the same thing)...????

I also tried to add "Next wsSht1" to the bottom of the code so it would progressed through the worksheets in the workbook, but unfortunately it failed...i've beening looking on the net to try and resolve it myself but i have to accept defeat on this simple task...(i know i need a "For" Statement to have a "Next" statement but im presuming it's got something to do with the "Set wsSht1 = Sheets("Sheet1")" code at the top of the Sub).

Rylo...sorry im not familiar with these Sheet Specific Macros...every time i try and run your code a Macro Dialog Box appears and asks me what Macro i want to run ?? Sometimes its blank and i have the coice to create one (which does nothing anyway)...and sometimes the "aaa" macro that Jeff created is there...am i meant to be running the Sheet Specific Code in collaboration with the "aaa" code Jeff has developed or should i be able to run it on its own ??

This is my first time using a forum for assistance guys and im starting to feel like a pain in the @ss that wont go away

...but im appreciative of everyones help with this...and i know its the best way for me to learn as well

**rylo** · 08-03-2007, 02:00 AM

Darren

Event macros aren't "run" in the usual sense. There are various triggers in the spreadsheet that causes the event code to run.

In this case, the trigger is a change in a cell.

So if you go to sheet depot 1 (2), and change cell D15 to 13/2/07, the macro will be automatically triggered and action. Change it to say 10/1/2015 and it should remove any background color.

The benefit of an event macro in this case is that it will run whenever the data is changed. You don't have to run a separate macro.

rylo

**boylejob** · 08-03-2007, 12:06 PM

Darren,

Stop worrying about how many posts you are putting out there on this problem. That is exactly what this forum is for and sometimes it takes quite a few posts to get through a problem.

I haven't got a clue about the date thing. I took your extract and ran it on my end with the colors already in place. It took the 01/03/09 and changed it from yellow to white. Appears to be working in America but not in Australia. On my end it is interpreting the date to be March 1, 2009. I just wonder if it is interpreting it to be January 3, 2009 on your end which would explain why it is changing it to yellow. I am going to send a private message to the administrator named mudraker who also lives in Australia and he might be able to shed some light on the subject.

Here is the modified code so that it will cycle through each worksheet in your workbook.

Please Login or Register  to view this content.

**mudraker** · 08-03-2007, 07:53 PM

I have found the American/Australian date problem to be a real pain in the butt.

I have hade macros running perfectly for months on a pc & then suddenly stop working correctly all because of this problem

The method I used to overcome the problem is to do as boylejob has done in his last post & convert the date to American format in a string variable. then compare this to todays date.

When I did this type of coding in the past as I did not know about the cDate command so I used datevalue command
Here is what I would have coded Using DateValue instead of Cdate & using Select Case instead of If commands

Please Login or Register  to view this content.

PS I am not saying using Datevalue is better than using Cdate

**boylejob** · 08-03-2007, 09:06 PM

mudraker,

What exactly is this doing

Please Login or Register  to view this content.

I understand the Find as being a search and use that all the time, but what is the "*" doing.

I knew I would learn something new.

**mudraker** · 08-04-2007, 03:16 AM

The * is a wild card - find any entry

As the search is coded to find previous it finds the last cell that has an entry in it

The following line of code searches by row until it finds that last entry.
Populates the lLR variable with the row number of the row the last entry was found in.

Please Login or Register  to view this content.

If I knew that column A would always have an entry in the last used row I would have used

Please Login or Register  to view this content.

I included the On Error Resume Next command to ignore the error that gets generated when doing a search on a blank sheet, and the On Error Goto 0 so that any errors generated after the On Error Goto 0 command are handled in the normal Excel VBA manner

This following command is to find the last used column - read above instructions subsituting column for Row.

Please Login or Register  to view this content.

**DarrenH** · 08-05-2007, 09:33 PM

Thanks for your help guys...

Yeah i've noticed that the macro assumes the dates are in American format...i've notice dates like 01/12/2009 and 02/08/2009 which are highlighted Yellow that should be white (based on the Aus Date Format)...

I've taken the code from Mudraker and added the Date Conversion code from Jeff below the Dim Statements...when i run it, Jeff's code is producing a error...I know i've got limited knowledge with this, but im not sure how this little bit of code is converting the dates to American format (maybe im missing something here or have mis-interpreted the previous post)...either way, i some how need the code to:

- convert the dates to American format
- run the Color Coding VBA
- convert the dates back to Australian format

Please Login or Register  to view this content.

Thanks again for your help with this guys...appreciate it

**mudraker** · 08-06-2007, 03:23 AM

I thought we had the dayes being converted & compared correctly.

Try this version

Please Login or Register  to view this content.

**DarrenH** · 08-06-2007, 09:46 PM

BINGO !!

I've had a look through my data and dates like 01/11/2009 are staying White as they should...

Ive got to play around with a few different colors and set some other conditions so i'll see how i go with this (need to try and get it to work myself to learn some more about VBA)...but it's hard being stuck in a office with no-one else that knows VBA...so i dare say i'll be asking some more questions shortly...

To Mudraker, Jeff & Rylo...Massive Thanks for your help with this...appreaciate the efforts you guys have put in

Til next time...

Cheers - Darren

**boylejob** · 08-06-2007, 10:23 PM

Darren,

That's GREAT! I'm glad it is finally working. This was definitely a group effort, but that is what this list is all about.

Keep playing around with it and keep your eye on this forum. I have learned a tremendous amount just reading other posts and reviewing the answers they get. I actually learned a few new things from you posts that I will be incorporating into some of my programs!

This is what it is all about. Look forward to your next question.

**DarrenH** · 08-08-2007, 02:56 AM

Ok guys...I think this is going to be an easy one...

I've had a little play around with it, just to include some other conditioning i didnt want to bother you guys with earlier...

The colour coding needed to change slightly to cater for our business...as you see in the code below i've change some parts of it...firstly, i've added a "dProjDate1" & "dProjDate2" which are mentioned in the "Case" Statements below...the code is now set up to:

- Color Red if <Todays Date
- Color Yellow if <Todays Date + 3 Months
- Color Purple if >Todays Date + 3 Months
- Color White if >Todays Date + 18 Months (this is where im having the problem)

Basically it's getting to the purple case and colouring every cell that is >Todays Date + 3 Months (and everything beyond)...I was hoping the Case Statement that follows it would then change it back to White (i know it's probably a lengthy way to do it, but i am still learning), however it's not working...Im pretty sure it's got something to do with the fact that once you tell it what to do (eg: colour Purple) then it's set like that...Can i set a condition to stop the purple Case at 18 months ??

I hope this makes sense...but just to clarify, i only want purple cells if the date is:

>Todays Date + 3 Months but less than 18 months.

Would it be better to use an IF/ELSE statement here ??

Please Login or Register  to view this content.

**mudraker** · 08-08-2007, 03:56 AM

DarrenH

In your code the variables dProjDate & dProjDate1 have the same date so there is no need for 2 seperate variables

When you added the new variables you did not declare them. Suggest you read http://www.ozgrid.com/VBA/variables.htm as to why you should declare your variables.

The problem with the Case statemeent is the order in which it is placed
When the code is run the 1st case statement is checked -
if the cell date is less than todays date (test = true) then that code is run and the other Case statements are ignored. If todays date is greater or equal to cell date then 1st Case statement is false & is ignored. Macro then checks the next Case statement.

As you had the check for date greater than 18 months after the check for dates greater than 3 months the code for the greater than 3 months will always be run. Moving the check for dates greater than 18 months before the check for dates greater than 3 months will solve your problem

Please Login or Register  to view this content.

**boylejob** · 08-08-2007, 10:42 AM

Darren,

Since you are doing all this color coding I thought you might find the following site useful. It gives you all the Excel colors in a chart that is laid out just like it appears in Excel. I printed a color copy out and have it setting on my desk and it comes in real handy.

http://www.mvps.org/dmcritchie/excel...htm#colorindex

**DarrenH** · 08-08-2007, 11:38 PM

Thanks Jeff...I was actually looking at that site earlier this week to get the colour ranges...cheers

OK...the codes working, the cells are being formatted correctly but there's one minor asthetic change that is annoying me...when i run the code it removes the gridlines from the cells that have been analysed (basically any cell that has a date in it)...

I've had a bit of a look on the net but couldnt find too much that helped...anyway, i've tried some code i found but it didnt seem to work (basically im just trying to apply the gridlines with a grey 25% colour - as it normally would be)...

Heres what im working with (it's just a small section of the code, i've placed it in the final Case Statement):

Please Login or Register  to view this content.

"ActiveWindow.GridlineColorIndex = 15" is the code that i've pasted in...I also tried ActiveSheet but it wouldnt work...Any suggestions to fix this...i've attached a small screenshot of the problem...

Cheers - Darren

**boylejob** · 08-09-2007, 12:24 AM

Darren,

In this section of code where you have a color index of 2 change it to xlNone and see if that fixes the problem.

Please Login or Register  to view this content.

**DarrenH** · 08-09-2007, 12:50 AM

Funny that...i actually had xlNone in there earlier...not sure why i changed it to "2" ??

But yeah, thanks Jeff, that's fixed it !!

**boylejob** · 08-09-2007, 01:10 AM

Darren,

Great! Glad I could help one more time!

For some reason we were all using the 2 instead of xlNone. We must have all been picking up on the word "white" in your original post. Anyway, glad xlNone fixed your problem.

**DarrenH** · 08-09-2007, 01:51 AM

I just Broke excel for the first time using VBA

After the problem with the gridlines disappearing i decided to try and add the normal Black Borders around all of the cells with data in them...i had a quick look on the net but decided to try and record the Macro myself and apply it...

So i started to record the Macro...in the active sheet i clicked "Ctrl+End" to go to last used cell in sheet, then "Ctrl+Shift+Home" to highlight all Cells up to A1...from there i applied the normal Black Cell Borders so that every cell that was highlighted was formatted...

I grabbed the code from VBA and pasted it into the code we've been working on, then ran it...for some reason it applied the black cell borders to all of the cells in all of the worksheets i have !! Excel crashed and the rest is history...Opps !!

Here's the code i use:

Please Login or Register  to view this content.

To me it looks pretty straight forward...can anyone see what i've done wrong...or am i just being a little ambitious in hoping it would be this easy ??

Cheers Guys

**boylejob** · 08-09-2007, 12:46 PM

Darren,

When you carve out all the unneccessary stuff the macro recorder give you this is what you need to add borders.

Please Login or Register  to view this content.

These two lines (the top two) are sometimes needed and sometimes they are not. On Error Resume Next will take care of the situation should they not be needed.

Please Login or Register  to view this content.

I have tried to code this to match everything else we have done up to this point. I did add the variable lrow2 and lcol2 to border a range of more than one cell. If you are wanting to incorporate this into the portion of code where the cell colors are changed, you can change lrow2 and lcol2 to lrow and lcol so it will look at a single cell.

Please Login or Register  to view this content.

Apply Color Coding to Dates based on conditioning

LinkBack

Thread Tools

Rate This Thread

Display

Apply Color Coding to Dates based on conditioning

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions