automatically color code dates in week long chunks?

**6stringslinger** · 03-22-2010, 02:06 PM

Hello,

I use a spreadsheet made by someone else at my office to track my hours at work. It automatically calculates overtime/weekend/holiday pay, which is handy. I use it by filling in the first column with dates (usually in sections mon-fri, but sometimes weekends too if I work them). I color code the weeks with three different colors (first listed week is Red, following week is Blue, third listed week is Yellow and then back to Red). I've been trying to figure out a way to have excel do this automatically but I'm not adept enough to figure this out. It seems that excel would have to look at the date, and determine if it belongs to the same 7 day week as the date above it. Then, if it DOES belong to the same week, use the same color as above, if if belongs to the following week, then use the next color in a given sequence. Could anyone advise on how this may be achieved? I'm pretty certain that excel does not have this feature built in, so it would have to be done via formula, or scripting, both of which I'll need pretty clear instructions on. Thanks in advance!

**davegugg** · 03-22-2010, 02:20 PM

Could you post a sample of how you would expect such a worksheet to look?
Thanks

**6stringslinger** · 03-22-2010, 02:39 PM

I've attached a jpeg screenshot Hopefully I've done this correctly =)

**davegugg** · 03-22-2010, 03:10 PM

Ok, this code will take a list of dates in column A starting at cell A3. It will go down the list to the last date and change the color based on Monday being the first day of the next week.

Please Login or Register  to view this content.

To use this, paste the above code in a standard module in the Visual Basic Editor. Then just run it.

Let me know if that works, or if you want something else. I couldn't think of a way to do this without using code, though I suspect there might be a complicated way to do it.

**6stringslinger** · 03-22-2010, 03:21 PM

Wow, I can't believe you just whipped that up! Thanks a million. It seems to work fine, but requires that I manually run the macro when I want it to occur. Is there a way to have this always monitoring the A column so as I fill in dates it automatically changes the color?

**davegugg** · 03-22-2010, 03:33 PM

Well, I could write something to look at the lowest date and if the font is black, evaluate what color it should be based upon your scheme.
Let me know if that is what you prefer.

It would be easiest to add this code to the worksheet:

Please Login or Register  to view this content.

This will recolor everything again including any changes you have made. It will always make the first week red. It runs whenever you change a cell in column A.

You have to put this code in the Worksheet Change Event of the worksheet that contains your data. To do this, double click that Sheet in the "Microsoft Excel Objects" in the VBA Project Browser on the left side of the VBE. Above the main coding area there are two comboboxes. First change the left one to Worksheet, then change the right one to Change. Then paste the guts of the code above in.