Conditional Formatting for a Date Range

[awalsh80]

I have a weekly date range at the top of the sheet that goes from Cells H1-BR1 (7/22/2011 - 8/31/2012)

There are 3 conditions:
1: There will be a start date (1 cell) that I want to be green
2: There will be a range (multiple cells) that I want to be blue
3: There will be a finish date (1 cell) that I want to be red

Example would be:

A2 (Activity 1)
B2 (Start date) 7/22/2011
C2 (Start of Range) 11/18/2011
D2 (Finish of Range) 12/23/2011
E2 (Finish Date) 4/20/2012

I would expect to see Green on cell H2
Blue on cells Z2-AE2
Red on AX2

Is this possible?

 

[Saagar]

Here is an idea.

Put this formula in cell H2 =IF(H1=$B$2,1,IF(H1=$E$2,3,"add"))
Add conditional formatting to the cells to be green when 1, blue when 2 and red when 3.
Now drag the function in H2 until BR.

You will notice:
- Start week has "1" with green color
- Finish date has "3" with red color
- Rest of the range has "add"

You will need to extend the formula and put another "IF" statement where it says "add" in the function (=IF(H1=$B$2,1,IF(H1=$E$2,3,"add"))) to add criteria for your range. You can make the formula put "2" when the weeks correspond to your range.

This should get what you want.

If you want to hide the numbers in the cells, in the conditional formatting, make the font color same as the cell color and that will hide the numbers.

Hope this helps.