Cell Referencing problem when copying conditional formatting

S

Sarah (OGI)

(Excel 2000)

I have a row of cells (K6:AH6) which have some conditional formatting,
depending whether or not the date (month and year) shown in the corresponding
headers in K5:AH5 are less than or greater than (up to 12 months) the date
given in cell D6.

I have managed to get this working for one row, using the following formula:

For the months that are less than the given date in D6 (up to 12 months
previous):
=AND(DATE(YEAR(K5),MONTH(K5),28)<DATE(YEAR($D$6),MONTH($D$6),28),DATE(YEAR(K5),MONTH(K5),28)>DATE(YEAR($D$6)-1,MONTH($D$6)-1,28))

For the months that are greater than the given date in D6 (up to 12 months):
=AND(DATE(YEAR(K5),MONTH(K5),28)>DATE(YEAR($D$6),MONTH($D$6),28),DATE(YEAR(K5),MONTH(K5),28)<DATE(YEAR($D$6)+1,MONTH($D$6)+1,28))

When I copy this down to the other rows, the cell reference changes where a
'$' has not been entered in the formula - so the references for the headers
then changes to subsequent rows. However, I change the formula to how I
think it should be represented, the conditional formatting doesn't work.
 
Top