Conditional formatting - Relative references (2010)

OK, so I have 2 columns. Column "N" contains a date of service which ranges from '09 to '13. Column "AQ" contains a date that must be within 12months prior to the date of service (Col. N). I attempted to apply conditional formatting to the range $AQ$8:$AQ$257 using the following formula:

=IF(YEAR($N8)=2012,IF($AQ8>$N8-366,0,1),IF($AQ8>$N8-365,0,1))

* The YEAR() function is a fail-safe to account for the leap year during the time period I am working with (2012).

The result is supposed to fill red any "1" results which are the dates that are greater than 12 months (errors). The formula works 100% if I use it in a helper column. However, if I use the formula as a conditional formatting the relative cell references are not changing. The only way to get the conditional formatting to apply properly is to either change the formula for every cell in my range (8:257) by hand or by copying and pasting the formatting by hand one cell at a time. This works but ends up creating a rule for every cell I want to do this in which is overbearing when viewing all conditional formatting in the worksheet.

Can anyone help me understand why the relative cell references are not changing when I use the painter tool or drag down the cell to apply my conditional formatting to my entire range?

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

http://www.excelforum.com/excel-form...es-2010-a.html

Thread Closed.