Conditional Formatting

[Colin Jarvis]

Hi

I Have a Worksheet with several columns that need
conditional formating. When I copy the conditioal
formatting using the format painter it does not change
the cell reference.

For example if A1 has a conditional format to B1 and I
copy it into A2 the conditional format still looks at B1,
however I want it to look at B2

As I have 6 columns of conditional data I dont want to
have to manually set up the conditions.

Any suggestions gratefully received

 

[David McRitchie]

Hi Colin,
See my page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If you can't see what your problem was then post the
formula used in cell A1. Sounds like you have used
or misused absolute cell addressing perhaps using
B$1 when you meant $B1 Also it would be better to
conditionally format *entire columns* rather than
using the format painter.

 

[Colin Jarvis]

Hi David

Many thanks for the tip. When I select a cell to
reference in the conditional format excel sets the cell
reference in the following format =$B$1 and then when I
paste it retains the value when I paste the format into
A2.

Your suggestion to change it to =$B1 enables me to copy
the conditional format into A2 and it will then reference
to B2 automatically.

Thanks again saved me a lot of work

Cheers from the UK

-----Original Message-----
Hi Colin,
See my page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If you can't see what your problem was then post the
formula used in cell A1. Sounds like you have used
or misused absolute cell addressing perhaps using
B$1 when you meant $B1 Also it would be better to
conditionally format *entire columns* rather than
using the format painter.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Colin Jarvis" <[email protected]>

wrote in message [email protected]...

 

[David McRitchie]

Good, you should be formatting the entire column if that
is what your intent is rather than extending it with the format painter,
it is easier and it will probably save space if C.F. works like regular
cell formatting.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi David

Many thanks for the tip. When I select a cell to
reference in the conditional format excel sets the cell
reference in the following format =$B$1 and then when I
paste it retains the value when I paste the format into
A2.

Your suggestion to change it to =$B1 enables me to copy
the conditional format into A2 and it will then reference
to B2 automatically.

Thanks again saved me a lot of work

Cheers from the UK

-----Original Message-----
Hi Colin,
See my page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If you can't see what your problem was then post the
formula used in cell A1. Sounds like you have used
or misused absolute cell addressing perhaps using
B$1 when you meant $B1 Also it would be better to
conditionally format *entire columns* rather than
using the format painter.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Colin Jarvis" <[email protected]>

wrote in message [email protected]...

.

 

[Steve]

-----Original Message-----
Hi

I Have a Worksheet with several columns that need
conditional formating. When I copy the conditioal
formatting using the format painter it does not change
the cell reference.

For example if A1 has a conditional format to B1 and I
copy it into A2 the conditional format still looks at B1,
however I want it to look at B2

As I have 6 columns of conditional data I dont want to
have to manually set up the conditions.

Any suggestions gratefully received


.

Check to see if the conditional format has changed from =if
(b1=1,true,false)to ="if(b1=1,true,false) if so remove the
quotes and should work.