Copy Conditional Formatting

[Tontonan]

I have a problem copying Conditional Formatting in Excel 2007

I am using the formula (shown below) in cell I1 to format that cell with
conditional formatting. When I copy this formula to cells I2 through to
I10, I wish to have the reference change from A1 to A2, A3 etc. to match the
cells I2, I3 etc

=A1>Today()

However, I end up with the same cell reference A1 and the 'applies to'
changes from I1 to I2:I10.

I have tried using copy & paste, format painter all give the same result.

I have read previous posts stating to ensure that A1 is not an absolute
reference which I believe I have done. Also I read a post somewhere that
this might be a bug in Excel 2007, but I can't find it again.

Any help would be appreciated, i have several hundred cells that I wish to
format.

Thanks

 

[OssieMac]

Hi Tontonan,

Copy the cell with the conditional format then select the other cells and
use Paste Special -> Formats

 

[OssieMac]

The other way is to select the cell with the conditional format then
Conditional format -> manage rules -> click the button at end of applies to
line and select then entire new range including the original cell.

Don't get confused by the formula shown in the Manage rules. It still
displays the original A1 but if you test it then you will find that it
applies to A2, A3 etc.

 

[Howard31]

Hi Tontonan,

Not just the formula in the sheet needs to use reletive references, Make
sure that the formula IN THE CONDITIONAL FORMAT DIALOG BOX uses reletive
references.

 

[Tontonan]

Ossiemac and Howard 31, thank you both for your assistance. It appears to
have worked.

I copied the formula from cell I3 to cell I4-I367, but I don't understand
how it works when you look at the formula in any cell from I4-I367, they all
have the formula =A4>Today() and the applies to box all have =$I$4:$I$367.

I would have thought that formula =A4>Today() would adjust for each cell it
was copied to in column I. i.e. A4 for I4, A5 for I5.

Regards,

Ben

 

[OssieMac]

Yes. It takes a little bit of understanding.

Because Manage Rules displays both the formula and the range that it applies
to, the formula that is displayed is for the first cell only of the applied
to range.

A couple of things to experiment with.
Set a rule for one cell only. (say I4)
Copy that cell and then select another range of cells (say I5:I10) and paste
special-> formats.
Open Conditional format -> Manage rules.
At the top of the dialog box set Show formatting rules for: This worksheet.
You will see that you now have two sets of rules. One for the original cell
and another for the range of cells to which you pasted. Note that the formula
for the pasted range now refers to the first cell of the pasted range.

Now copy cell I4 again and this time select I4:I10 (Includes the original
copied cell) and paste special -> formats.
Open Conditional format -> Manage rules again and set Show formatting rules
for: This worksheet.
You will see that you now have only one set of rules because it has included
the entire range as one rule because you pasted over top of the original cell.

The above is really only a demonstration. In practice you are probably
better to simply open Conditional format -> Manage rules and reset the range
it applies to (Including the original cell). Not sure if you know this but
just in case. To reset the applies to range, click the icon at the right of
the applies to field, select the required range and then click the icon at
the right to return to the Conditional format Manage rules dialog box.

 

[Tontonan]

Makes sense now.

Thanks again

Reagards,

Ben

Yes. It takes a little bit of understanding.

Because Manage Rules displays both the formula and the range that it applies
to, the formula that is displayed is for the first cell only of the applied
to range.

A couple of things to experiment with.
Set a rule for one cell only. (say I4)
Copy that cell and then select another range of cells (say I5:I10) and paste
special-> formats.
Open Conditional format -> Manage rules.
At the top of the dialog box set Show formatting rules for: This worksheet.
You will see that you now have two sets of rules. One for the original cell
and another for the range of cells to which you pasted. Note that the formula
for the pasted range now refers to the first cell of the pasted range.

Now copy cell I4 again and this time select I4:I10 (Includes the original
copied cell) and paste special -> formats.
Open Conditional format -> Manage rules again and set Show formatting rules
for: This worksheet.
You will see that you now have only one set of rules because it has included
the entire range as one rule because you pasted over top of the original cell.

The above is really only a demonstration. In practice you are probably
better to simply open Conditional format -> Manage rules and reset the range
it applies to (Including the original cell). Not sure if you know this but
just in case. To reset the applies to range, click the icon at the right of
the applies to field, select the required range and then click the icon at
the right to return to the Conditional format Manage rules dialog box.