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.

On Jul 7, 8:47 am, Rob wrote:
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.

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" wrote:

On Jul 7, 8:47 am, Rob wrote:
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.

On Jul 11, 7:56 am, Rob wrote:
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.