How to do Conditional Formatting in a Calendar

[BADebbie]

I need to make each Monday a different color based on a rotation of colors. Is this possible? I have 17 different colors that we will rotate through throughout the year. 2016 Inspection Sched SurfMnt.xlsx


I have attached a file of what I want it to look like.

[Pete_UK]

Your colours don't seem to follow a strict rotation. For example, 21st March is dark grey (number 14), but then the next Monday is shown as red (01), but the following Monday (4th April) is colour 17 and the next Monday is colour 03.

I was going to suggest having sequential numbers to the side (in columns Z to AB), which could then be used for conditional formatting of the Mondays, but it won't work if the rotation is not strictly sequential.

Pete

[BADebbie]

I noticed that too. I will check with my boss, and see if he wants it correct.

[BADebbie]

We will be having it done in the exact rotation. How would I put the numbers in columns Z to AB?

[Pete_UK]

I've set this up for you in the attached file. I've amended the calendar layout slightly so that the 1st day of the month always appears on the first row. You can set the starting code for the first Monday in January in cell AB2 (just enter any number - it will be displayed as 2 digits), and then the formulae in the pale blue areas of columns Z to AB will automatically adjust, incrementing up to the number of codes that are being used (17 in this case) and then starting again.

I've also applied the necessary CF rules for every Monday - those in January, April, July and October making use of the numbers in column Z, and similarly for the other months.

Hope this helps.

Pete

[BADebbie]

Thank you so much Pete. It looks great!!!

[BADebbie]

My boss would prefer a calendar that he can change with a button. I have a attached the calendar. How do we make this one do the same thing you did on the other one? Thank you.

[Pete_UK]

I spent several hours putting the other one together for you. I am not willing to do the same again, especially as you have flipped the months over so you now have 3 months down and 4 across.

Perhaps someone else will contribute to this thread.

Pete

[BADebbie]

I understand. Thank you for all your help/work.