Pasting internal cell format from 1 cell to another

[Guest]

Being new to this and no VB expert I'm trying to write a script to copy
internal cell format values from 1 sheet/cell to another.

I am using ".PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
skipBlanks:=False, Transpose:=False" and this works for the cell format
itself.

However if I go into the cell content (the fx line) and (for example) BOLD
the first three word and then change the font colour of the 12th word, then
the pastespecial command does not seem to work.

What should I do ensure the formatting internal to a cell is pasted accross?

Thx.

 

[Guest]

In that case, the color property applies to the text or characters object and
not the cell object. Only the cell format properties are pasted with the
paste special. However, If you copy the cell and just paste, then the font
color does stay with the single word. Or you can format the entire cell for
font color and it will transfer the format because it is applied at the cell
level instead of the text or character level.

 

[Guest]

I'm afraid this didn't work.

Replaced pastespecial in: " With
ActiveWorkbook.Sheets("XXX").Range("A9:S9").Copy
End With
With ActiveWorkbook.Sheets("YYY").Range("A4:S4")
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
skipBlanks:=False, Transpose:=False
End With"

to read: "With ActiveWorkbook.Sheets("YYY").Range("A4:S4").Paste
End with"

The text was copied but the internal formatting wasn't.