Forum Discussion
Conditional formatting - keep formatting
Hallo. I have the following issue. I have to compare the values of two worksheets. All differences will be highlighted with a special background color. That works without any issues.
But, in the end I want to forward the worksheet with the highlighted backgrounds to other persons without any condional formatting rules. In other words, how can I keep the formatting of the cells? I already tried to use "Paste Special -> Values and Source Formatting", but this also pastes the conditional formatting rules. I only want to have the colors, not the rules. Your help is highly appreciated.
2 Replies
There's no built in way to do this.
I found this article on using VBA to do it
http://excel.tips.net/T001947_Removing_Conditional_Formats_but_Not_the_Effects.html
I also saw a suggestion to copy the data into Word and then back into Excel, which on the face of it appears to work (if it's just values) but not sure how practical that is, or if there are other consequences.
Hi Wyn,
that's really a smart idea to copy the table into WORD and then copy it back to Excel. I tried that with OneNote instead of WORD (because of the non-existing page size restrictions) and it worked without any issues.
Now I have all my values and background colors in an Excel sheet without any conditional formattings. That's exactly what I've been looking for. Thanks a lot!
Regards, Lutz
