Conditional Format and Colour and Date Reference

Hello,

I am looking at a way to reduce the number of conditional formats I use?

I have a simple (but very long) date lookup query on my worksheet which uses conditional formatting to show a cell background colour depending on a date lookup.

I have yearly date bands, with different colours throughout the year, starting in Cell A7.

06-Nov 20-Dec - blue
21-Dec 26-Dec - light orange
27-Dec 28-Dec - pink
29-Dec 30-Dec - red
31-Dec 31-Dec - blue

In Cell A1, I have a calculated date, 15-12-12, for example.

In conditional formatting, I have 5 rules, if between 06-11-2012 to 20-12-2012, then cell A1 = blue. etc, etc. see the jpg (manager-1.jpg)

However, this is just a small sample. I have 21 date ranges (with 6 different colours) and for years 2012, 2013, etc.

I know excel 2003 has a limit of 3 rules, while Excel 2007 allows many more.

However, to have 22 rules per year for 1, 2 or more years, is a lot of coding. Is there a way, I can make a formula, for example per colour? or per year?

so, between 06-11-2012 and 20-12-2012 or 31-12-12 to 31-12-12 = blue
or between 06-11-2012 and 20-12-2012 or 06-11-2013 and 20-12-2013 = blue

and so on?

see the excel-sheet for a sample selection (cond-form-date.xlsx)

This may not be the best way to achieve what I need to to do. So maybe a VLOOKUP will work, but can that be combined with conditional formatting?

I have already found out that conditional formatting can not be used to lookup a cell in a separate worksheet, so the date calendar needs to be displayed on every worksheet.

Ultimately I have a range of dates and I would like to know which date band a particular date falls into. I am using colour to signify this at the moment but open to other suggestions.

Thanks for any ideas!
Andy