expanding custom formatting without removing existing cell formatting?

[Keith]

We had to do this quickly yesterday (XL2003), and ended up doing it manually
(ugh) but I have to believe there is a better way...

Starting with a worksheet that has cells color-coded manually (each cell
background color) as either light blue, dark blue, or green

Then added a conditional format in one cell (to test and make sure it was
working properly) with three conditions, which either leave the cell the
original color (light blue, dark blue, green) or changes it (three
conditions) to yellow, orange, or red based on the contents of that cell and
other cells. The conditional formatting uses relative and fixed cell
references (for example, in cell D17 one condition was =AND($B17 = $A$1, D17
<> "M")

I then wanted to copy the conditional formatting /only/ to the rest of the
cells in the target range, so that the relative cell reference (D17) will
update to whatever cell the conditional formula is pasted to (e.g. paste in
E17 it becomes =AND($B17 = $A$1, E17 <> "M").

However, when I copy/paste special/format, it also copies the original cell
color.

In the end, to get the work done, we manually re-colored the grid after
pasting the conditional formatting in all cells. I've got to believe there
is some way (without a bunch of VBA) to expand conditional formatting to a
range of cells without also copying the underlying cell color, but while we
were trying to find a way yesterday I wasn't able to find anything...

Many thanks,
Keith

 

[RagDyeR]

Select *all* the cells that you wish to conditionally format after you're
finished manually coloring them.

THEN ... create your formats, aiming cell references to the cell in focus of
the selection (first cell).

Careful with your absolute and relative references.

--

HTH,

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

We had to do this quickly yesterday (XL2003), and ended up doing it manually
(ugh) but I have to believe there is a better way...

Starting with a worksheet that has cells color-coded manually (each cell
background color) as either light blue, dark blue, or green

Then added a conditional format in one cell (to test and make sure it was
working properly) with three conditions, which either leave the cell the
original color (light blue, dark blue, green) or changes it (three
conditions) to yellow, orange, or red based on the contents of that cell and
other cells. The conditional formatting uses relative and fixed cell
references (for example, in cell D17 one condition was =AND($B17 = $A$1, D17
<> "M")

I then wanted to copy the conditional formatting /only/ to the rest of the
cells in the target range, so that the relative cell reference (D17) will
update to whatever cell the conditional formula is pasted to (e.g. paste in
E17 it becomes =AND($B17 = $A$1, E17 <> "M").

However, when I copy/paste special/format, it also copies the original cell
color.

In the end, to get the work done, we manually re-colored the grid after
pasting the conditional formatting in all cells. I've got to believe there
is some way (without a bunch of VBA) to expand conditional formatting to a
range of cells without also copying the underlying cell color, but while we
were trying to find a way yesterday I wasn't able to find anything...

Many thanks,
Keith

 

[Keith]

Sounds good- I tried creating a reference that included the full range
(crazy, yes) so for example if I selected C17:M142, I was testing variations
of
=AND($B17:$M17 = $A$1, C17:M142 <> "M")
Clearly, that doesn't work ;-)

I'll try your suggestion the next time we need to update that workbook.
Thank you,
Keith

 

[RagDyeR]

Just make sure you reference *only* to the first cell in focus.

Appreciate the feed-back.
--

Regards,

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

Sounds good- I tried creating a reference that included the full range
(crazy, yes) so for example if I selected C17:M142, I was testing variations
of
=AND($B17:$M17 = $A$1, C17:M142 <> "M")
Clearly, that doesn't work ;-)

I'll try your suggestion the next time we need to update that workbook.
Thank you,
Keith