How to preserve character formatting in a cell

[Kobus]

I need to add and remove characters from the end of cells in an
existing worksheet. Some of the existing characters in the cells have
character formats like bold or italic.
The problem is that I cannot find a way to preserve the existing
character formatting in the cells. The following example will lose all
character formats in a cell.
ActiveCell.Value = ActiveCell.Value & "@"
It is easy to do by hand but it seems impossible through VBA.
Is there an easy way of preserving the existing character formats?

 

[Gary Keramidas]

this doesn't change formatting for me, unless i'm missing something. if it's
bold, it stays bold, if it's bold and italic, it stays bold and italic.
ActiveCell.Value = ActiveCell.Value & "@"

 

[Sharad]

Funny. Like Gary said, works for me too.
Try a work around by copying the cell and pasting the formats:

ActiveCell.Copy
ActiveCell.Value = ActiveCell.Value & "@"
ActiveCell.PasteSpecial xlPasteFormats

Sharad

 

[Tom Ogilvy]

the OP is talking about Rich Text formatting, where individual characters
within the cell have different formatting. Some individual characters may
be bold, but not all characters are bold for example. This certainly
exhibits the behavior the OP described.

 

[Tom Ogilvy]

Assuming Rich Text Formatting as you describe:

This example maintains the bold and Italic character formatting. You would
need to add an array for each formatting attribute you want to maintain :

Sub AppendData()
Dim rng As Range, l As Long
Dim i As Long, arrBold() As Variant
Dim arrItalic As Variant
Set rng = ActiveCell
l = Len(rng)
ReDim arrBold(1 To l)
ReDim arrItalic(1 To l)
' Capture the formatting
For i = 1 To l
arrBold(i) = rng.Characters(i, 1).Font.Bold
arrItalic(i) = rng.Characters(i, 1).Font.Italic
Next
rng.Value = rng.Value & "@"
For i = 1 To l
rng.Characters(i, 1).Font.Bold = arrBold(i)
rng.Characters(i, 1).Font.Italic = arrItalic(i)
Next
End Sub

 

[Sharad]

Right Tom!, That explains it.

the OP is talking about Rich Text formatting, where individual characters
within the cell have different formatting. Some individual characters may
be bold, but not all characters are bold for example. This certainly
exhibits the behavior the OP described.

 

[Kobus]

Right Tom!, That explains it.






- Show quoted text -

Thank you. I think Tom got a solution. Lets hope that the only
formatting is bold and italic. I presume it would be difficult to
check for all formatting (such as colours, underline subscript,
superscipt, caps, etc.).
I was hoping that VBA would have a more elegant solution.