Conditional Formattin withe Dates

[ranjuv]

Hello everyone,

I am new to the forum. My problem is that i am trying to apply conditional formatting to a range of cells which contains certain dates. But the formatting just takes the initial cell data for all the other cells. I tried various methods like removing the $ sign between the range but it doesnt help. The table looks like below:

Actual Date Expected Finish Status
10.2.2012 17.02.2012 Achieved
02.03.2012 16.03.2012 Achieved
15.05.2012 17.05.2012 Under Process
15.06.2012 15.06.2012 Not Achieved

So there is a date 15.05.2012 to which the dates has to compared and the formatting has to be applied. What i wanted to find is if the actual date is less than the current date, and the status is achieved, then the whole data from that column becomes yellow. If the condition is false, then the cells should be left as it is. The formula which i used for formatting is =IF(AND($A8<$G$7;$D8="achieved");1). It works for the first cell but then when i copy the format to all other cells using paste special, its not happening. Any help would be appreciated.

Thanks,
Ranju

[Fotis1991]

Hi Ranju and welcome to the forum.

In C.F rules just "fix" your range that you want to fill with color...

[sathiyamoorthy]

can you give me sample sheet with expected answer in next book.

[ranjuv]

Hey guys,

I think i am ok now. It was just that i was expecting the data range to change in every cell the conditional formatting was applied which was not the case. I mean in cell A9, i was expecting to show the formula including A9 and in A11 with A11 and so on.

Sorry for the trouble. It was my mistake.

Thanks,

Ranju