Find a specific character with text strings and then change the formatting of the specific character only

advertisements

Requirement
I have an excel sheet with hundreds of rows and columns. Each cell is filled with random character that are times new roman, size 10, regular.
I need to find a single character "·" that is randomly placed in some cells and not in others.
Once I find this character I need to change the format to symbol, size 9, and bold.

Problem I cannot use the "find all" and "replace all" feature because that changes the entire cell format. How do I write a code to find and replace/change the "·" symbol format? I am very amateur with VBA.

Here's what I have attempted, but it does not work.

 Sub bnm()
     With Selection.Find
        .ClearFormatting
        .Text = "·"
        .Replacement.ClearFormatting
        .Replacement.Text = "·"
        .Name = "Symbol"
        .FontStyle = "bold"
        .Size = 9
        .Execute Replace:=wdReplaceAll, Forward:=True, _
            Wrap:=wdFindContinue
    End With
End Sub

Please help! Thank you.


I'm sure this could be prettier, but it works:

Sub Macro1()
    Dim firstRange As Range
    Dim currentRange As Range
    Set firstRange = Cells.Find(What:="·", LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
    Set currentRange = firstRange
    While Not currentRange Is Nothing
        Dim val As String
        Dim lastIndex As Integer
        val = currentRange.Value
        lastIndex = InStr(lastIndex + 1, val, "·")
        While lastIndex <> 0
            With currentRange.Characters(Start:=lastIndex, Length:=1).Font
                .Name = "Symbol"
                .Size = 9
            End With
            lastIndex = InStr(lastIndex + 1, val, "·")
        Wend
        Set currentRange = Cells.FindNext(After:=currentRange)
        If firstRange = currentRange Then
            Set currentRange = Nothing
        End If
    Wend
End Sub

A limitation is that this only works if the formatting doesn't work if the cell value is determined via a formula for example a cell containing ="test: " & "·" cannot have special formatting for the ·