Bold and red specific type of text in selected cell

[evonne_j]

I am writing a VBA to bold and red the text in a cell with the following condition

- any words insided "<" and ">" or "{" and "}" (e.g. "xyz <abc> xyz <123>" becomes "xyz <abc> xyz <123>" or "xyz {abc} xyz <123>" becomes "xyz {abc} xyz <123>"

the following code is what i try, it work normally, but when I test for the code with some strange string, for example, my selected cell with text starting by some strings which already in bold format, e.g. "a ""<abc>"" a" or "xyz <abc>", they will return "a <abc> a" (the bold format of first "a" disappear)
"xyz yz <abc>" (bold format of "xyz" disappear, "yz" added in between and "<abc>" did not turn red, the strangest thing is that "xyz <abc>" is still showing on formula bar which is different from what the cells value (xyz yz <abc>) display

Is there any bugs I did not notified? thanks.

Sub redbold()
    Dim cell As Range
    Dim pos, startpos As Long
    Dim targetref As Boolean
     
    For Each cell In Selection
         

        For pos = 1 To Len(cell.Value)

        If (Mid(cell.Value, pos, 1) = "<") Or (Mid(cell.Value, pos, 1) = "{") Then
            startpos = pos
            targetref = True

        ElseIf targetref And (Mid(cell.Value, pos, 1) = ">" Or Mid(cell.Value, pos, 1) = "}") Then
                With cell.Characters(startpos, pos - startpos + 1).Font
                    .ColorIndex = 3
                    .Bold = True
                End With
            targetref = False
        End If
        
        Next pos
        
    Next cell
End Sub

 

[evonne_j]

my test samples as following

 

[evonne_j]

is there anyone can help?? thanks

 

[vds1]

Assuming Column A has input values you can try this code. Test this code in a new sheet.

You have to run Format_All macros,

Sub Format_All()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Call Format_BoldRed("<", ">")
Call Format_BoldRed("{", "}")
End Sub


Private Sub Format_BoldRed(Str1 As String, Str2 As String)
For i = 1 To Range("a" & Rows.Count).End(xlUp).Row
 StrtPos = InStr(Cells(i + 1, 1).Value, Str1)
 If StrtPos > 0 Then
    lnth = InStr(Cells(i + 1, 1).Value, Str2) - StrtPos + 1
    Cells(i + 1, 1).Select
    With ActiveCell.Characters(Start:=StrtPos, Length:=lnth).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Color = -16776961
    End With
 End If
Next i
End Sub

Hope this helps!

 

[evonne_j]

Thank you vds for your good try, but I found that my topic issue still exist. will this be a problem of MS Office? because I am currently using Office 2010 and do not have other version.

My purpose of this code is to bold and red any text in selected cell which is bracket by "<" ">" or "{" "}" pairs and do not change the format of other text in the cell.