Search and Replace With Formatted Text

[Taxi Houston]

How can I use VBA code to replace text in one cell with formatted text
from another cell?

Here is an example.

Cell a1 contains text that may include italicized or bolded characters.
The content and format of cell a1 is controlled by the user.

Cell b1 contains text to search for and does not contain character
formatting.

Cell c1 contains the target text, some of which will be replaced. It
does not contain character formatting.

How can I search c1 for the text in b1 and replace it with the contents
of a1 while keeping any formatted characters in a1?

I have no trouble doing the search and replace, but cannot figure out
how to preserve any formatted text.

Any suggestions?

Taxi

 

[Myrna Larson]

You need to use the Characters property of the Range object. IMO, this isn't
pretty and will probably be quite slow.

But here goes...

Sub CopyCharacterFormats()
Dim Src As Range
Dim Dest As Range
Dim i As Long
Dim j As Long

Set Src = Worksheets("Sheet2").Range("A1")
Set Dest = Worksheets("Sheet2").Range("C1")
Dest.Value = "xxx" & Src.Value & "yyy"

j = 4 'must be set to point where you inserted the text

For i = 1 To Len(Src.Value)
Dest.Characters(j, 1).Font.Bold = Src.Characters(i, 1).Font.Bold
Dest.Characters(j, 1).Font.Italic = Src.Characters(i, 1).Font.Italic
j = j + 1
Next i

End Sub