Copying Conditional Formatting to range of cells

[Guest]

I am setting up a very simple, one-step, conditional format to hide a cell if
it is displaying an error.

The formula is: =iserror(relative cell ref)
The format is: font color = white

I then try to copy those cells, but the whole range turns white.
I then cannot even manually adjust the font color for those cells, as they
still render as white.

And oddly even if I manually retype the whole conditional formatting (in a
cell where I attempted to copy the conditional formatting) it still won't
render properly. I have to delete the conditional formatting, and then
retype.

Can anyone give me some pointers?

Thanks,

-b

 

[RagDyeR]

Try using the "Format Painter" tool (yellow paintbrush) from the toolbar.

Click in the cell with the CF,
Click on the paintbrush,
Click in the first cell you want copied, and drag down.

If the cells you want to CF are not contiguous,
*Double Click* on the paintbrush,
And then simply click in each cell that you want to CF.

Hit <Esc> to exit the format copy mode.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I am setting up a very simple, one-step, conditional format to hide a cell
if
it is displaying an error.

The formula is: =iserror(relative cell ref)
The format is: font color = white

I then try to copy those cells, but the whole range turns white.
I then cannot even manually adjust the font color for those cells, as they
still render as white.

And oddly even if I manually retype the whole conditional formatting (in a
cell where I attempted to copy the conditional formatting) it still won't
render properly. I have to delete the conditional formatting, and then
retype.

Can anyone give me some pointers?

Thanks,

-b

 

[T. Valko]

What cells have the conditional formatting?

What cells are you copying? Are you copying just the formatting or the
entire cell contents?

Where are you pasting after the copy?

 

[Guest]

I have a range h4:h100, so I set up the conditional formatting in h4 and am
copying h4, and pasting it into h5:h100. I am copying/pasting the entire cell
contents (I even looked at Paste Special to see if there was a 'Conditional
Formatting' check box). So the pasting is copying the Conditional Formatting,
but copying the absolute cell ref, and not making it relative (so $h$4 shows
up in every conditional format formula). BTW, I copied with both the paste
function, and the drag function, and the same result.

The odd thing is that when I retype the proper cell reference, it still
renders the font white, when it shouldn't. The only way I can make it work is
if I delete the Conditional Formatting, and retype it. And even though it is
typed exactly as it was, it now renders properly.

I am now using =if(iserror([ref]),"",[ref] which which also delivers the
desired results, but am still perplexed by the behavior of Conditional
Formatting when copied.

 

[T. Valko]

Ok, it sounds like you just need to make the conditional formatting
reference a relative reference.

=ISERROR(H4)

I even looked at Paste Special to see if there was a
'Conditional Formatting' check box

There is a Paste Special>Formats and this will include any conditional
formatting. That's how I copy formats. I've never used the Format Painter.
In fact, I took it off my toolbar.

--
Biff
Microsoft Excel MVP

I have a range h4:h100, so I set up the conditional formatting in h4 and am
copying h4, and pasting it into h5:h100. I am copying/pasting the entire
cell
contents (I even looked at Paste Special to see if there was a
'Conditional
Formatting' check box). So the pasting is copying the Conditional
Formatting,
but copying the absolute cell ref, and not making it relative (so $h$4
shows
up in every conditional format formula). BTW, I copied with both the
paste
function, and the drag function, and the same result.

The odd thing is that when I retype the proper cell reference, it still
renders the font white, when it shouldn't. The only way I can make it work
is
if I delete the Conditional Formatting, and retype it. And even though it
is
typed exactly as it was, it now renders properly.

I am now using =if(iserror([ref]),"",[ref] which which also delivers the
desired results, but am still perplexed by the behavior of Conditional
Formatting when copied.

What cells have the conditional formatting?

What cells are you copying? Are you copying just the formatting or the
entire cell contents?

Where are you pasting after the copy?

 

[RagDyeR]

OP stated that he started with using a relative reference.

BTW - The use of Format Painter is supposedly very intuitive.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Ok, it sounds like you just need to make the conditional formatting
reference a relative reference.

=ISERROR(H4)

I even looked at Paste Special to see if there was a
'Conditional Formatting' check box

There is a Paste Special>Formats and this will include any conditional
formatting. That's how I copy formats. I've never used the Format Painter.
In fact, I took it off my toolbar.

--
Biff
Microsoft Excel MVP

I have a range h4:h100, so I set up the conditional formatting in h4 and am
copying h4, and pasting it into h5:h100. I am copying/pasting the entire
cell
contents (I even looked at Paste Special to see if there was a
'Conditional
Formatting' check box). So the pasting is copying the Conditional
Formatting,
but copying the absolute cell ref, and not making it relative (so $h$4
shows
up in every conditional format formula). BTW, I copied with both the
paste
function, and the drag function, and the same result.

The odd thing is that when I retype the proper cell reference, it still
renders the font white, when it shouldn't. The only way I can make it work
is
if I delete the Conditional Formatting, and retype it. And even though it
is
typed exactly as it was, it now renders properly.

I am now using =if(iserror([ref]),"",[ref] which which also delivers the
desired results, but am still perplexed by the behavior of Conditional
Formatting when copied.

What cells have the conditional formatting?

What cells are you copying? Are you copying just the formatting or the
entire cell contents?

Where are you pasting after the copy?

 

[T. Valko]

Hmmm...

Any chance that calculation is set to manual? Check and make sure
calculation is set to automatic.

--
Biff
Microsoft Excel MVP

OP stated that he started with using a relative reference.

BTW - The use of Format Painter is supposedly very intuitive.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Ok, it sounds like you just need to make the conditional formatting
reference a relative reference.

=ISERROR(H4)

I even looked at Paste Special to see if there was a
'Conditional Formatting' check box

There is a Paste Special>Formats and this will include any conditional
formatting. That's how I copy formats. I've never used the Format Painter.
In fact, I took it off my toolbar.

--
Biff
Microsoft Excel MVP

I have a range h4:h100, so I set up the conditional formatting in h4 and
am
copying h4, and pasting it into h5:h100. I am copying/pasting the entire
cell
contents (I even looked at Paste Special to see if there was a
'Conditional
Formatting' check box). So the pasting is copying the Conditional
Formatting,
but copying the absolute cell ref, and not making it relative (so $h$4
shows
up in every conditional format formula). BTW, I copied with both the
paste
function, and the drag function, and the same result.

The odd thing is that when I retype the proper cell reference, it still
renders the font white, when it shouldn't. The only way I can make it
work
is
if I delete the Conditional Formatting, and retype it. And even though it
is
typed exactly as it was, it now renders properly.

I am now using =if(iserror([ref]),"",[ref] which which also delivers the
desired results, but am still perplexed by the behavior of Conditional
Formatting when copied.

What cells have the conditional formatting?

What cells are you copying? Are you copying just the formatting or the
entire cell contents?

Where are you pasting after the copy?

--
Biff
Microsoft Excel MVP


I am setting up a very simple, one-step, conditional format to hide a
cell
if
it is displaying an error.

The formula is: =iserror(relative cell ref)
The format is: font color = white

I then try to copy those cells, but the whole range turns white.
I then cannot even manually adjust the font color for those cells, as
they
still render as white.

And oddly even if I manually retype the whole conditional formatting
(in a
cell where I attempted to copy the conditional formatting) it still
won't
render properly. I have to delete the conditional formatting, and then
retype.

Can anyone give me some pointers?

Thanks,

-b

 

[David Biddulph]

Yes, but CF has a dreadful habit of changing relative references to
absolute, (and changing formulae to strings). It's always worth going back
into CF to check that you've got the condition you intended.
Putting the = sign at the beginning of the formula, rather than letting
Excel insert it, will probably reduce the likelihood of Excel
second-guessing your intentions, but it's always safeer to check.
--
David Biddulph

OP stated that he started with using a relative reference.

BTW - The use of Format Painter is supposedly very intuitive.

Ok, it sounds like you just need to make the conditional formatting
reference a relative reference.

=ISERROR(H4)

I even looked at Paste Special to see if there was a
'Conditional Formatting' check box

There is a Paste Special>Formats and this will include any conditional
formatting. That's how I copy formats. I've never used the Format Painter.
In fact, I took it off my toolbar.

--
Biff
Microsoft Excel MVP

I have a range h4:h100, so I set up the conditional formatting in h4 and
am
copying h4, and pasting it into h5:h100. I am copying/pasting the entire
cell
contents (I even looked at Paste Special to see if there was a
'Conditional
Formatting' check box). So the pasting is copying the Conditional
Formatting,
but copying the absolute cell ref, and not making it relative (so $h$4
shows
up in every conditional format formula). BTW, I copied with both the
paste
function, and the drag function, and the same result.

The odd thing is that when I retype the proper cell reference, it still
renders the font white, when it shouldn't. The only way I can make it
work
is
if I delete the Conditional Formatting, and retype it. And even though it
is
typed exactly as it was, it now renders properly.

I am now using =if(iserror([ref]),"",[ref] which which also delivers the
desired results, but am still perplexed by the behavior of Conditional
Formatting when copied.

What cells have the conditional formatting?

What cells are you copying? Are you copying just the formatting or the
entire cell contents?

Where are you pasting after the copy?

--
Biff
Microsoft Excel MVP


I am setting up a very simple, one-step, conditional format to hide a
cell
if
it is displaying an error.

The formula is: =iserror(relative cell ref)
The format is: font color = white

I then try to copy those cells, but the whole range turns white.
I then cannot even manually adjust the font color for those cells, as
they
still render as white.

And oddly even if I manually retype the whole conditional formatting
(in a
cell where I attempted to copy the conditional formatting) it still
won't
render properly. I have to delete the conditional formatting, and then
retype.

Can anyone give me some pointers?

Thanks,

-b

 

[Miles Jordan]

I've been struggling with the very same thing. It appears to me that when you apply a conditional formatting rule to more than one cell, you need to be sure the references in the formula are absolute. Even though they look correct when you view the conditional formatting for one of the other cells it will not display the correct formatting.

A second problem appears to be when you copy conditional formatting using copy / paste special. I can only get it to work correctly by using the format painter as described previusly in this post.

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com