This sounds like it's going to be a difficult problem to solve. It sounds
like you will need to save each character's font attributes of each cell
being changed and then reset the text after it is replaced in the cell. Hope
your deadline is not too soon. Here are some examples of setting and finding
individual character's attributes in a cell:

Dim i As Long

Cells(1, 1).ClearFormats
Cells(1, 1) = "Hello World"
Cells(1, 1).Characters(1, 5).Font.Bold = True

For i = 1 To Cells(1, 1).Characters.Count
If Cells(1, 1).Characters(i, 1).Font.Bold Then
MsgBox Cells(1, 1).Characters(i, 1).Text & " is BOLD"
End If
Next i


" wrote:

I have a large number of macros that change the formating of cells (fonts, etc).
I'm having trouble replacing text on cells that contain multiple fonts in the same cell. Replacing any any text will changethe formating in the cell. Can anyone help me because on a deadline and cant figure this out.

The problem can be recreated easily by putting a line of text into a cell and making part of the cell BOLD. Now do a search and replace on any part of the cell. The BOLD text will no longer be bold.

Try something like this (run it in the Workbook_Open procedure)

Dim i As Long
Dim OneChr() As String
Dim IsBold() As Boolean
Dim FntName() As String

Cells(1, 1).ClearFormats
Cells(1, 1) = "Hello World"
Cells(1, 1).Characters(1, 5).Font.Bold = True
Cells(1, 1).Characters(1, 5).Font.Name = "Courier New"
MsgBox "Continue"

ReDim OneChr(Cells(1, 1).Characters.Count)
ReDim IsBold(Cells(1, 1).Characters.Count)
ReDim FntName(Cells(1, 1).Characters.Count)

For i = 1 To Cells(1, 1).Characters.Count
OneChr(i) = Cells(1, 1).Characters(i, 1).Text
IsBold(i) = Cells(1, 1).Characters(i, 1).Font.Bold
FntName(i) = Cells(1, 1).Characters(i, 1).Font.Name
Next i

Cells(1, 1).ClearFormats
MsgBox "Continue"

For i = 1 To UBound(OneChr)
Cells(1, 1).Characters(i, 1).Text = OneChr(i)
Cells(1, 1).Characters(i, 1).Font.Bold = IsBold(i)
Cells(1, 1).Characters(i, 1).Font.Name = FntName(i)
Next i


"Charlie" wrote:

This sounds like it's going to be a difficult problem to solve. It sounds
like you will need to save each character's font attributes of each cell
being changed and then reset the text after it is replaced in the cell. Hope
your deadline is not too soon. Here are some examples of setting and finding
individual character's attributes in a cell:

Dim i As Long

Cells(1, 1).ClearFormats
Cells(1, 1) = "Hello World"
Cells(1, 1).Characters(1, 5).Font.Bold = True

For i = 1 To Cells(1, 1).Characters.Count
If Cells(1, 1).Characters(i, 1).Font.Bold Then
MsgBox Cells(1, 1).Characters(i, 1).Text & " is BOLD"
End If
Next i


" wrote:

I have a large number of macros that change the formating of cells (fonts, etc).
I'm having trouble replacing text on cells that contain multiple fonts in the same cell. Replacing any any text will changethe formating in the cell. Can anyone help me because on a deadline and cant figure this out.

The problem can be recreated easily by putting a line of text into a cell and making part of the cell BOLD. Now do a search and replace on any part of the cell. The BOLD text will no longer be bold.