Excel Conditional formatting - extending the formatting to a range of cells in a column does not work

Hello all,

I am trying to do what I think is a rather basic conditional formatting exercise.

However, I am not having much luck.  Here is what I am trying to do.

1. There are a set of values in columns B and C - data for one week.
2. I need to format C3 as follows:

    Fill with Red if C3 is less than B3

    Fill with Green if C3 greater than or equal to C3.

3. I need to extend the conditional formatting to rows C4 thru C5 and C7 thru C9 (C3 thru C9 including C7 is also acceptable.)
4. I also need to replicate B3:C9 for the remaining 50 weeks in the year.

I am able to apply conditional formatting to C3 and can get it behave the way I want it to.

However when I try to extend it by dragging the handle down to C9, the formatting seems

to get messed up.  All the cells in the range C4 thru C9 are comparing their value to B3 instead of

B4, B5, etc.

Another intriguing behavior I see is that if I create the rule manually for each cell, then changes made to

rules for any cell seem to be getting duplicated to all the other cells as though there is only one conditional

formatting specification.

I need a solution that works in Excel 2010 as well as Excel 2007.

Any help I can get is greatly appreciated.  Thanks in advance for all the help.

Regards,

Raghu