Copy apperance of cells?

[JenniferMurphy]

Is there a way to copy the "appearance" of a range of cells without any of the formulas or the conditional formatting rules?

I have a row of values that are the result of formulas and several conditional formatting rules. The resulting values have a variable number of decimal points and then have several different fill colors applied depending on the rules.

I would like to copy the row to another row in the same sheet so that it looks exactly like the source row, but without any formulas or rules. That is, it would be as if the values are literals and the fills were applied manually.

I tried Alt+Ctrl+C twice, first selecting Values and then Formats. That works, but it replicates the rules, which ends up creating hundreds of unnecessary rules.

Thanks

 

[JenniferMurphy]

Jennifer,
I just found an instance where I had multiple formulae as well as colourings etc.

I found that if I selected that range and then CTRL + V I relocated the contents (including formulae which were relative) BUT then without leaving that location I selected Paste Options and then accessed Values I have overwritten the formulae. So, in the first instance I've copied everything as required, formulae are not referencing properly, but the second paste does as you seemingly require.

Yes?

No, I don't think so. I can't follow what you are saying, so I'm not 100% sure. Copying values and then formats gets what I want, but it also copies the conditional formatting rules (not the formulas), which I do not want.

 

[JenniferMurphy]

The format painter will only copy the formatting, cells colours and borders. no values or formulas will be pasted.

Yes, the format painter, like copying formats, also copies just the formatting -- no values or formulas. But it also copies the conditional formatting rules (not the formulas), which I do not want. I don't know how to say any more clearly.

 

[JenniferMurphy]

If you only have Office 2007, then your macro would have a lot of work to do evaluating all the conditional formats to see what is active; for 2010 and later you can simply read the DisplayFormat of the cells to get the formats that are showing.

I guess I'll wait to try it when we upgrade.

Edit: I just remembered that I've done this before by pasting the HTML from the clipboard - see this post: in VBA : Copy formatting without copying the rules from a Conditional Formatted cell

Interesting. I'll give that a try if I get ambitious.

Thanks