CODEWITHSUNDEEP
excelvba
johndoe253
johndoe253

Reputation: 237

replace function removes formatting vba

enter image description hereWhy does this remove the formatting of the cell? I just want to replace a value but it removes underlines from all words within the cell.

For Each sht In ActiveWorkbook.Worksheets
      sht.Cells.Replace what:=fnd, Replacement:=rplc, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Next sht​

Upvotes: 0

Views: 981

Answers (1)

Slai
Slai

Reputation: 22886

Seems like in Excel 2007 the whole cell value is replaced. If the value to replace is between formatting, you might be able to replace it in the cell value XML:

Dim r As Range, s As String
Set r = sht.UsedRange.Find(fnd)
s = r.Value(11) ' Excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet = 11
s = Replace(s, ">" & fnd & "<", ">" & rplc & "<")
r.Value(11) = s

Otherwise you can try this http://www.mrexcel.com/forum/excel-questions/524889-find-replace-loses-text-properties-formatting.html

Dim r As Range, i As Integer
Set r = sht.UsedRange.Find(fnd)
i = InStr(r.Characters.Text, fnd)
r.Characters(i, Len(fnd)).Insert rplc

Upvotes: 1

Related Questions