How do I retain formatting?

[Jock]

The following line of code will copy data from one sheet (Wbs) to another
(Wbd):

Wbd.Range("C" & lngNewRow).Value = wbS.Range("E" & lngRowNo)

How can I adapt the above to retain the formatting from the source sheet?

 

[Rick Rothstein]

The default property for a range is Value, so your statement is only
assigning one value to another cell's value. If you use the range's Copy
method, then you bring everything about the cell over. Give this a try...

wbS.Range("E" & lngRowNo).Copy Wbd.Range("C" & lngNewRow)

 

[Jock]

That's better.
It copies comments also - can I narrow it down so it only copies the text
and the format the text is in (i.e bold or red)?

 

[Rick Rothstein]

You have to be more specific when you ask your questions. Your original
message said...

"How can I adapt the above to retain the formatting from the source sheet?"

To answer the question you have now posed, you will have to tell us how the
text got colored... manually or via Conditional Formatting (CF)? If it was
colored manually, then you can do this...

Range("C20").Value = Range("C16").Value
Range("C20").Font.ColorIndex = Range("C16").Font.ColorIndex

If, on the other hand, the color came from CF, then you will need to perform
the same test(s) in code that you used in the CF and assign the appropriate
ColorIndex as per the test result(s).

 

[Javed]

You have to be more specific when you ask your questions. Your original
message said...

"How can I adapt the above to retain the formatting from the source sheet?"

To answer the question you have now posed, you will have to tell us how the
text got colored... manually or via Conditional Formatting (CF)? If it was
colored manually, then you can do this...

Range("C20").Value = Range("C16").Value
Range("C20").Font.ColorIndex = Range("C16").Font.ColorIndex

If, on the other hand, the color came from CF, then you will need to perform
the same test(s) in code that you used in the CF and assign the appropriate
ColorIndex as per the test result(s).

--
Rick (MVP - Excel)






- Show quoted text -

Dear Jock Try this

wbS.Range("E" & lngRowNo).Copy
Wbd.Range("C" & lngNewRow) .pastespecial paste:=xlpastevalues
Wbd.Range("C" & lngNewRow) .pastespecial paste:=xlpasteformats

It is not required to know the format manual or Conditional,excel will
paste all formt.

 

[Rick Rothstein]

Hmm, you know Javed, you may be on to something here.<g> For some reason, I
was going for a manual coloring of the text in the "copied" cell; but, of
course, imposing the same Conditional Formatting on the "copied" cell as
existed in the original cell would have the same visual effect. I'm not sure
why that little subtlety escaped me...

Jock, do what Javed has suggested. Watch out for the extra space that crept
into the two PasteSpecial statements in front of the dot for the
PasteSpecial method calls. Also, you may want to follow Javed's code lines
with this one in order to remove the "marching ants" around the cell being
copied...

Application.CutCopyMode = 0