Excel 2007 Conditional Formatting only copies absolute

[Rob]

I'm trying to conditionally format an entire table so that the rows highlight
rew when a date is passed. However, when I copy the format the reference for
both column and row remain absolute rather than changing as the row moves up
or down. I'm using the formula:
=IF(TODAY()>=($A436+14)*AND(ISBLANK($F436)),0,1)
So that if something is two weeks after the inital date and the cell where a
completed should have been entered is blank, then FALSE is returned and the
cell will highlight red.

 

[Spiky]

I'm trying to conditionally format an entire table so that the rows highlight
rew when a date is passed. However, when I copy the format the reference for
both column and row remain absolute rather than changing as the row moves up
or down. I'm using the formula:
=IF(TODAY()>=($A436+14)*AND(ISBLANK($F436)),0,1)
So that if something is two weeks after the inital date and the cell where a
completed should have been entered is blank, then FALSE is returned and the
cell will highlight red.

Try selecting all the appropriate cells first, then do the conditional
format.

 

[Rob]

I tried that, but if I try to enter the formula it will be specific to
whichever row I enter, such as the 436 used in the formula, and I want the
formatting to reference the data in each row since the dates and thus the due
date will vary. Also, I tried entering the entire A column and F column, but
received an error. Thanks for the idea, though Spiky.

 

[Spiky]

I tried that, but if I try to enter the formula it will be specific to
whichever row I enter, such as the 436 used in the formula, and I want the
formatting to reference the data in each row since the dates and thus the due
date will vary. Also, I tried entering the entire A column and F column, but
received an error. Thanks for the idea, though Spiky.

Hmm. It works properly in version 2003. Are you sure it kept the
absolute/relative references properly? It likes to assume all is
absolute in the conditional format window.

Also, you may not actually need the "IF". If possible, design a
formula that gives a TRUE to change the highlight color, then you have
fewer details.