copying cells with conditional format colors, pasting in new worksheet keeping colors

[schueyisking]

Hi,

I've got a spreadsheet which I have conditionally formatted with cells that are elsewhere. I want to be able to copy the data I have filtered, and paste it to another spreadsheet. The problem I've got is when I copy and paste it doesn't paste the colours, because the colours are produced from the conditional formatting, and the formulas for those conditional formats are on the original spreadsheet.

There must be a way to get excel to copy and paste the colours of conditionally formatted cells?

found another person with the same question -
'I have used conditional formatting to identify certian parameters. I must

now copy that column to another spreadsheet but do not want to lose that

highlighted data. I have tried Copy Paste Special but I can only copy the

Values. Is there any way to copy those cells and the colors?'



would really appreciate some help on this, thanks.

[dominicb]

Good morning schueyisking

What you want to do is certainly no simple task due to the way Excel handle conditionally formatted cells : the only way to determine the colour (which you have to do before you can start setting the cell colours) is to interrogate the CF formula, and then apply that logic to see if the case is true or not.

Dave Peterson has provided some code here that seems to do the trick (look at post 8), in a thread that seems originally to have propagated through Excel Forum some time back in 2003.

HTH

DominicB

[dominicb]

Wow

I've just looked at post 9 in the above thread - and you know what ... ?
It only works. Amazing.

DominicB

[schueyisking]

thank you for the response,

i'll give it a go and report back