Find and replace words that are formatted with tags

[AA_20069]

Hi All,

Ive done a search already, and the results don't seem to resolve a problem i have.

Within my spreadsheet on columns G & H each cell contains a couple sentences with some words formatted

e.d. Go to this place and do this. Then afterwards submit this item. Something in bold and italics etc etc etc

what i would like to happen is that VBA(?) would then be able to identify the bold and format the wording as follows

Go to <b>this place</b> and <b>do this</b>. Then afterwards <b>submit this item</b>.Something <b><i>in italics</i></b>

This would need to happen on each cell in rows g & h?

any helps would be appreciated.

Many thanks.

[westconn1]

you can try like this

Dim cel As Range, tmp as String
Dim bb As Boolean, bi As Boolean
For Each cel In Range("g:h")     'change to used range
    If Not IsEmpty(cel) Then
        For ch = 1 To cel.Characters.Count
            If cel.Characters(ch, 1).Font.Bold And Not bb Then
                bb = True
                tmp = tmp & "<b>"
                ElseIf Not cel.Characters(ch, 1).Font.Bold And bb Then
                    bb = False
                    tmp = tmp & "</b>"
            End If
            If cel.Characters(ch, 1).Font.Italic And Not bi Then
                bi = True
                tmp = tmp & "<i>"
                ElseIf Not cel.Characters(ch, 1).Font.Italic And bi Then
                    bi = False
                    tmp = tmp & "</i>"
            End If
            If bb Then tmp = tmp & "</b>"
            If bi Then tmp = tmp & "</i>"
            cel.Value = tmp
            tmp = tmp & cel.Characters(ch, 1)
        Next
    End If
Next

i did not test this so may contain error or typo, test before using, the cell result will no longer contain formatting, if you want to retain formatting as well as tags, you would need to use a range (cell) to hold temporary value, in place of string

[AA_20069]

Hi westconn1,

Many thanks for your reply. This sort of does the trick; expect for the following:
1) it replaces the entire cell value (if it finds bold) with <b></b> rather then adding the tags around the phrase of the word i.e. <b>this part is bold tag me</b>, this part isnt leave me alone
2) its failing at line

tmp = tmp & cel.Characters(ch, 1)

with the following error : Run-time Error '438' Object doesn't support this property or method.

Many thanks for your assitance in this

[westconn1]

2. change to
tmp = tmp & cel.Characters(ch, 1).text

i changed the above line and tested the code on your string in first post correct result was obtained, except the order of bold and italic close tags together

i put the result in a different cell for testing purposes only
Go to <b>this place </b>and do this. Then afterwards <b>submit this item</b>. Something in <b><i>bold and italics</b></i> etc etc etc