Appending to a Cell doesn't keep format of existing content

[DejaUser]

I am writing some macros that appeds to a cell (using CHR(10) for line
breaks). I can't figure out a way to format each line differently
(i.e. bold, normal, italic, red, blue, etc.):

For example, I want to write following in cell A1 dynamically using
VBA:

=====================================

John Doe (BOLD)
James Smith (NORMAL)
Rob Smith (BOLD)

After the macro runs, all 3 lines above are NORMAL. After the 1st line
was written, it was bold, but then it lost it's formatting after the
2nd and 3rd lines were appended.

Here is my code to format data:

Private Sub formatData (valueToAppend as String, gender as String)
Dim iStart As Integer
Dim iLen As Integer

' apply format only to the data being appended. Don't touch
existing data
iLen = Len(valueToAppend)
iStart = Len(Range("A1").Value)
If (iStart >= iLen) Then
iStart = (iStart - iLen) + 1
Else
Exit Sub
End If

' Make females bold
If (gender = "F") Then
Range("A1").Characters(iStart, iLen).Font.Bold = True
End If

' reset the font to Normal for the following text
Range("A1").Characters(iStart + iLen, iStart + iLen + 1).Font.Bold
= False

End Sub

=====================================

I'd appreciate any help greatly.

 

[Cath]

Dejauser

try replacing your
Font.Bold = true by

Font.FontStyle = "bold"
and then
Font.FontStyle = "Regular"

Cath

 

[DejaUser]

Thanks. I'll give that a try.

I belive the problems is that when I read the existing cell value to
append to it, Excel returns the existing value as a plain text. So,
when I append something to it and format it, format is kept only for
the latest value and it has lost the formatting for the old values.

 

[DejaUser]

So, is there anyway to APPEND to a cell leaving existing data's
formating (bold, color, etc.) intact?

Whenever I append new data to a cell, it loses the existing data's
formatting.