Search and Replace With Formatted Text

[Taxi Houston]

A little tricky, but with a little finagling it works great. Muc
obliged. I'm not sure what the <<Dest.Value = "xxx" & Src.Value
"yyy">> line is all about. I had to remove it to get it to work.

Taxi


Myrna Larson
Guest

Re: Search and Replace With Formatted Text
You need to use the Characters property of the Range object. IMO, thi
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

 

[Myrna Larson]

I used that line when testing the code on a blank worksheet and forgot to
take it out before posting. You did the right thing.