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

 

[BrianJN1]

Are you looking for:
Select the range of cells,
Copy,
Go to the first destination cell
and then Paste/Values?

 

[JenniferMurphy]

Are you looking for:
Select the range of cells,
Copy,
Go to the first destination cell
and then Paste/Values?

As I said, I tried Copy + Paste Values. That gets rid of the formulas, but it does not preserve the formatting.

If I follow that with Copy + Paste Formats, then I get all of the formatting, but I also get the Conditional Formatting Rules, which I do not want. I want the results of the rules, but without the actual rules. This copy is going to be done hundreds or even thousands of times and I do not want that many rules cluttering up the sheet.

 

[djdbg1]

Will the format painter function not do what you require? Highlight the cells of which you want to copy the format. Click the format painter icon, (it's like a paintbursh) then highlight the cells you wish to be formatted. Ensure the same volume is selected each time.

 

[BrianJN1]

Without a VBA macro I don't see any other option.

I believe that some of our more knowledgeable persons may be able to suggest a means to create a function/macro for something that has been highlighted.
Whilst composing this a comment has just come in. I looked and wondered. Might that do?

 

[JenniferMurphy]

Will the format painter function not do what you require? Highlight the cells of which you want to copy the format. Click the format painter icon, (it's like a paintbursh) then highlight the cells you wish to be formatted. Ensure the same volume is selected each time.

I believe the format painter is the same as Copy + Paste Formats. No? In any case, it does give me exactly what I want from an appearance standpoint, but it also copies the rules, which I do not want.

 

[JenniferMurphy]

Without a VBA macro I don't see any other option.

I believe that some of our more knowledgeable persons may be able to suggest a means to create a function/macro for something that has been highlighted.

I considered a macro, but unless it can "see" the highlighting (fills), it would have to replicate the rules and then if I changed the rules, I would have to remember to change the macro. Not ideal.

 

[BrianJN1]

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?

 

[djdbg1]

I believe the format painter is the same as Copy + Paste Formats. No? In any case, it does give me exactly what I want from an appearance standpoint, but it also copies the rules, which I do not want.

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

 

[RoryA]

I considered a macro, but unless it can "see" the highlighting (fills), it would have to replicate the rules and then if I changed the rules, I would have to remember to change the macro. Not ideal.

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.

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