Conditional format a range

Hello,

I have set a conditional format of a row of cells to

=VALUE($D$1)=5

so that if this cell = 5 i can change the background colour of the entire row.

I then selected the row and dragged the handle down to select the required range of cells and selected "Fill Formatting Only"

My problem is the conditional formatting formula doesn't change the row number automatically and I have to go into each row and change the formula accordingly.

Is there a way to avoid this as it will take a long time.

Any help would be appreciated.

I am running Excel 2003 sp3 Standard edition on XP

Doh! Silly error. Thanks a whole bunch