There's a slight bug in this code.

I added one line.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim wks As Worksheet
Dim isBold As Variant
Dim myPfx As String
Dim mySfx As String
Dim inBoldSequence As Boolean
Dim NewString As String
Dim myChar As String

Set wks = Worksheets("Sheet1")
myPfx = "<strong"
mySfx = "</strong"

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no text constants!"
Exit Sub
End If

For Each myCell In myRng.Cells
If Len(myCell.Value) = 0 Then
'do nothing
NewString = myCell.Value '<---Added
Else
NewString = ""
isBold = myCell.Font.Bold
If isBold = True Then
'whole cell is bolded
NewString = myPfx & myCell.Value & mySfx
ElseIf IsNull(isBold) Then
'mixture of bold and non-bold
inBoldSequence = False
For iCtr = 1 To Len(myCell.Value)
myChar = Mid(myCell, iCtr, 1)
If myCell.Characters(iCtr, 1).Font.Bold = True Then
'found a bold character
If inBoldSequence Then
'do nothing special, just add the character
NewString = NewString & myChar
Else
'turn it on
inBoldSequence = True
'add the prefix
NewString = NewString & myPfx & myChar
End If
Else
'found a regular character
If inBoldSequence Then
'turn it off
inBoldSequence = False
'add the suffix
NewString = NewString & mySfx & myChar
Else
'not in bold, just add the character
NewString = NewString & myChar
End If
End If
Next iCtr
'check to see if last character was bold
If inBoldSequence Then
'if it was, add the suffix
NewString = NewString & mySfx
End If
End If
End If
myCell.Value = NewString
Next myCell
End With

End Sub



Dave Peterson wrote:

This version worked ok for me--ignore the formatting when it's done: