Makes sense now.
Thanks again
Reagards,
Ben
"OssieMac" wrote:
> 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.
>
> --
> Regards,
>
> OssieMac
>
>
> "Tontonan" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > > --
> > > Regards,
> > >
> > > OssieMac
> > >
> > >
> > > "OssieMac" wrote:
> > >
> > > > Hi Tontonan,
> > > >
> > > > Copy the cell with the conditional format then select the other cells and
> > > > use Paste Special -> Formats
> > > >
> > > > --
> > > > Regards,
> > > >
> > > > OssieMac
> > > >
> > > >
> > > > "Tontonan" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > >
|
Similar Threads
