How to shade a cell when a date falls within a certain date range

I'm trying to create a spreadsheet that displays shaded cells based on whether a task falls within a date range. Also the date range is being calculated so it is a formula and not just a date value. The date range is the result of a table above where I can change a project's tasks duration in order to see where it falls within the calendar year. Example: Shade cell C2 if cell C1 (row 1 contains the static dates, like a weekly calendar) is equal or greater than the date in cell A2 (my start date that is based on a formula) and is equal or less than the date in B2 (my end date that is based on a formula). If it does not fall within the range then do not shade. I would then repeat this for the next cell D2 and E2 and across the row to the end of the year. I am using Excel 2007

I forgot one important detail in the above: C1 is a date representing the start of the week and the next cell, C2 is the start of the next week. If I use days vs weeks your solution works well but I really want to display the week vs each day to reduce the number of columns needed. Is there a way to alter the formula so that it says shade C2 if the start (A2) and end (B2) dates fall within the date range of C1 through D1? To simplify I'm really just trying to shade all the weeks a task is being performed VS the days.

Hi Coedog,

For Excel 2003 and earlier:
1. Select cells C2:xx2 (where xx = your last used column in row 2)
2. Click Format -> Conditional Formatting
3. Under 'Condition 1', select 'Formula Is' from the drop-down list
4. Next to that put the formula: =AND(C1>=$A$2,C1<=$B$2)
5. Click the 'Format' button and choose a shading/font/etc.
6. Click OK twice to return to your sheet.

That should do it.

UPDATE, I see you're using Excel 2007. I'll post those instructions momentarily.

For Excel 2007:
1. Select cells C2:xx2 (where xx = your last used column in row 2)
2. On the Home ribbon, click 'Conditional Formatting' -> New Rule...
3. Beneath 'Select a rule type', choose 'Use a formula to determine which cells to format'
4. Beneath 'Format values where this value is true', enter: =AND(C1>=$A$2,C1<=$B$2)
5. Click the 'Format' button and set your formats
6. Click OK twice to return to your sheet.