copy conditional formatting, paste format without conditions

[jasonr17]

Having created a set of cells w/ simple conditional formats ( x >5, cell fill
is green, x=0 cell is grey, etc.), I would like to copy this grid of cells,
and paste grid w/ same color pattern to another area in the work sheet.

IE, 2x2 grid, top two cells are green, bottom two cells are grey (have
numbers in them causing the coloring) I would like to copy this 2x2 grid,
paste somewhere else without the numbers, just the formats.

Doable?

Thanks

 

[FSt1]

hi
on the menu bar...
edit>pastespecial>fomats>ok

Regards
FSt1

 

[jasonr17]

Thanks for the reply, I dont' think i was clear enough...

I would like to paste the current format (as i see it) to another area of
the worksheet, I don't want the conditional formats to follow.

i.e., if the conditional formats created a checkerboard of green's and greys
because of the numbers in the cells, I would want to copy and paste just the
checkerboard colors, w/ no conditional formats.

hope this helps?



I want the

 

[Gord Dibben]

Think over what you just asked and you will see the lack of logic in it.

The conditional formats are creating the color scheme of greens and grays
depending upon the values in the cells.

Without pasting the CF and the values, you will lose the colors because there is
no reason for them to remain.

CF colors do not work the same as regular background colors and cannot be
detected in VBA code because they have no Interior.ColorIndex value.


Gord Dibben MS Excel MVP

 

[Shamantiks]

I have been trying to do the same thing Jason. I want to keep my formatting
and discard the code. This will allow end users to resort a spreadsheet,
copy/paste and keep all the colors/formatting but not have it change on them.
I need the formatting static.

So Gord you make a valid point that the interior colorindex isn't populated
the same way just shading a cell green or red would. I'll take that as fact.
But there must be a way! Hasn't someone at least written a macro or module
that can identify the formatting of each, and copy it into tab2 without the
conditional formatting? something clever?

Obviously the issue is caused by the morons at Microsoft who failed to test
Excel. "Paste Special - Formats" should do the trick. But unfortunately, they
never realized that "Formats" actually includes Formats and Formulas (and
Conditional Formatting).

Pasting Values = values only. Pasting Formulas = formulas only.
So why doesn't Pasting Formats = Formats only?

Hard to believe there is no solution/workaround to this design flaw/bug.
I've done alot of web searches and nobody has a working answer I can find.

 

[Gord Dibben]

Did you visit Chip's site to see code for determining CF Conditions and pulling
the colorindex from the "activecondition"?

http://www.cpearson.com/excel/CFColors.htm



Gord

 

[Shamantiks]

Brillian find Gord - thank you! This looks like a monster of code to
understand for me but I'll give it a crack. Very impressed someone figured
this out, and of all my searches this appears to be the only solution.
Gracias!

-$