Excel 2010, Looking for VBA to copy Cells with Conditional Formatting to another sheet and paste the contents including the CF Formats

Hi Experts,

I've looked around quite a bit but haven't found a solution yet.  I'm looking for VBA to copy a Range of Cells with Conditional Formatting (in Excel 2010) to another sheet and paste the contents including the Conditional Formatting Formats (but without the Rules).  Restated, I want the copied range to look just like it does on the source sheet but since the Conditions don't exist on the destination sheet, I need a way to Paste the fully formatted contents).

I've seen suggestions elsewhere about Pasting to Word and then copying back to Excel but I'm hoping there's a fully automated solution here.

Thanks for anyone's insights.

Jeff
LVL 2
Jeffrey SmithOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Conditional Formatting is based on the rule(s) that user defined. Without copying the rule(s) means there is no Conditional Formatting copied and applied to the targeted range.

or is that mean you want to copy and paste the values, with format?
Jeffrey SmithOwnerAuthor Commented:
Thanks for posting, Ryan.

No, I need to copy the values with all Formats, including the CF, but as a static format because the Conditions won't exist in the New Sheet and the Rules wouldn't work.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I actually don't quite understand your requirement.

The sample attached is enabled user to copy and paste with CF and cell formatting.

it just seems working for me.

or you can tell us what's your requirement based on this sample attached?
28698278.xlsm
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Ryan

Your code is simply copying everything from the source range to the destination range.
I think that he wants to copy everything but the conditional formatting rules and wants to retain the colors applied by the the conditional formatting in the source range.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>I think that he wants to copy everything but the conditional formatting rules and wants to retain the colors applied by the the conditional formatting in the source range

Then I think it needs to do a quite complex verification on conditional formatting's rules in which we need to determine the rules' sequences and formulas that matched the conditions (cell's data), and then grab the associated color accordingly, and then finally applied to the targeted cells.

Not yet writing a test script for this, but is this worth to do this? Reason being... If we managed to write the script above, the result is as same as when we applied the conditional formatting. From a user prospective of view, it's the same. Hence, for this reason, it not persuade me to write the similar script like this.
Jeffrey SmithOwnerAuthor Commented:
Thanks for joining the discussion, sktneer.  And yes, your interpretation is exactly what I need.

@Ryan:  
... but is this worth to do this? Reason being... If we managed to write the script above, the result is as same as when we applied the conditional formatting. From a user prospective of view, it's the same.

Let me clarify:  In one scenario, I have 9 separate CF Rules that apply to one Row in the Source ws, and I want to copy that Row as it appears to a new ws.  The Rules include references to Conditions that only exist (in various places) on the Source ws, so copying the Source Row to a new sheet would not work because those References don't exist in the destination (new) ws.

In another scenario, I have a range of AutoFiltered Rows that have 2 CF Rules that apply to them.

Hope this is clear.
Jeffrey SmithOwnerAuthor Commented:
I've found a page that might help solve this, but it seems to deal with preserving the CF of a given Range in place and then deleting the CF.

Haven't sorted out how to apply this to my situation yet (where I am Pasting the Source Range to another newly created sheet, AND I don't want to Close the Source ws without Saving as that question proposed (because the Source ws is in the same wb as the Destination ws), but it sounds promising:

Jeffrey SmithOwnerAuthor Commented:
Huh, I had posted the Link in my previous comment but must have also deleted it somehow, too.  Anyway, here it is:

http://stackoverflow.com/questions/25768023/excel-copy-conditional-formatting-remove-rules-keep-format

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey SmithOwnerAuthor Commented:
Ok, I have got this sorted out.  Basically, I copied the entire Source ws, then ran that sheet through the code at the link I posted above so that I now had static values in that new sheet.  Then I copied from that new sheet just the ranges I wanted to a 2nd new ws, and then deleted the 1st new ws. I will close this out.
Jeffrey SmithOwnerAuthor Commented:
Solved problem myself.  Thanks for having a look at this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.