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

--
Rick (MVP - Excel)



"Javed" wrote in message
...
On Apr 22, 10:45 pm, "Rick Rothstein"
wrote:
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)

"Jock" wrote in message

...



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)?
--
Traa Dy Liooar

Jock

"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)

"Jock" wrote in message
...
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?
--
Traa Dy Liooar

Jock

.- Hide quoted text -

- 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.