Conditional formatting -- copy paste special won't change referenc

I am setting conditional formatting on a rather large spreadsheet for sum
values by month.

Cell A1 is the Resired resource request (data input)
Cell A2 =Sum(A3:A5)
Cell A3 through A5 are data input fields.

I can set conditional formatting to change fill based on greater than/less
than. What I cannot do is perform a bulk operation that will change my
conditional value for columns B through AH. Copy/paste special...formats
will add the conditional format, but KEEP the original reference.

Any way to change the in the same fashion as a traditional formula does when
you copy/paste?

Cheers....

Originally Posted by Ed in Biotech

I am setting conditional formatting on a rather large spreadsheet for sum
values by month.

Cell A1 is the Resired resource request (data input)
Cell A2 =Sum(A3:A5)
Cell A3 through A5 are data input fields.

I can set conditional formatting to change fill based on greater than/less
than. What I cannot do is perform a bulk operation that will change my
conditional value for columns B through AH. Copy/paste special...formats
will add the conditional format, but KEEP the original reference.

Any way to change the in the same fashion as a traditional formula does when
you copy/paste?

Cheers....

Basically, it involves putting a formula into the conditional format and using a $ as you would with a normal formula.

See the following page: http://www.contextures.com/xlCondFormat02.html

The site has other pages on conditional formatting, here's the index page: http://www.contextures.com/tiptech.html

Scott

Set the CF for the first cell but be sure to use relative addressing as in =A1
and not =$A$1

You can then copy the format to other cells using the format paintbrush or paste
special>formats


Gord Dibben MS Excel MVP

On Thu, 20 Jul 2006 10:28:02 -0700, Ed in Biotech
<EdinBiotech@discussions.microsoft.com> wrote:

>I am setting conditional formatting on a rather large spreadsheet for sum
>values by month.
>
>Cell A1 is the Resired resource request (data input)
>Cell A2 =Sum(A3:A5)
>Cell A3 through A5 are data input fields.
>
>I can set conditional formatting to change fill based on greater than/less
>than. What I cannot do is perform a bulk operation that will change my
>conditional value for columns B through AH. Copy/paste special...formats
>will add the conditional format, but KEEP the original reference.
>
>Any way to change the in the same fashion as a traditional formula does when
>you copy/paste?
>
>Cheers....
>

Perfect -- thanks!

Ed

"Ed in Biotech" wrote:

> I am setting conditional formatting on a rather large spreadsheet for sum
> values by month.
>
> Cell A1 is the Resired resource request (data input)
> Cell A2 =Sum(A3:A5)
> Cell A3 through A5 are data input fields.
>
> I can set conditional formatting to change fill based on greater than/less
> than. What I cannot do is perform a bulk operation that will change my
> conditional value for columns B through AH. Copy/paste special...formats
> will add the conditional format, but KEEP the original reference.
>
> Any way to change the in the same fashion as a traditional formula does when
> you copy/paste?
>
> Cheers....
>
>