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

D

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

Cath

Dejauser

try replacing your
Font.Bold = true by

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

Cath
 
D

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top