Not copying conditional formats

[BillRobPV]

I have condtionally formatted cells in a sheet. I want to copy the cells to
another sheet, preserving the cells colors (as defined by the conditional
format), but I do NOT want the conditional format parameters to apply to the
new sheet. "Paste Special Values" doesn't do it. Do I have an option set up
somewhere that insists on copying the conditional format "formulas" as well
as the colors?? TIA

 

[Gord Dibben]

From Cjip Pearson's site we get this information.................

Unfortunately, the Color and ColorIndex properties of a Range don't return
the color of a cell that is displayed if Conditional formatting is applied
to the cell.

.................................................

For more info and code required to capture the index of the CF color see
Chip's pages at

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

You can determine the color of the source cell and apply to the target cell
but you will need a macro to do that.


Gord Dibben MS Excel MVP

 

[BillRobPV]

Thanks. Looks tougher than I thought. The current spreadsheet only uses
Excel functions, but I do write macros (not well!). In plain english, I
guess this is my code (I have about 20 columns and 1600 rows, and some
conditional formatting is cell value, and some is "formula is"):
Select a column
For each row in the column, what is the color index of the cell?
If not clear (color number 0???) then
Selection.Interior.ColorIndex = "nn"
next row, next column, etc.

My question is, does this clear the conditional formatting? If not what
would be the proper command??? I will read the Pearson website, but I
thought I would check this also. I am a litlle confused about using a macro
to sense the color of a cell based on conditional formatting. Thanks Bill
Roberts

 

[Gord Dibben]

As Chip points out...............you cannot return the colorindex number
from a CF colored cell so your pseudo-code won't return a number.

Best to start with Chip's site and look at his Functions for determining CF
colors.

The trick will be when doing the copy/paste operation to determine the CF
color and paste that along with the value.

Or do the copy/paste operation then run a macro to find those cells with CF
conditions, determine the colorindex and set that while removing the CF.

Nobody said it was easy....................although there might be someone
watching this thread who has made it so.


Gord