CODEWITHSUNDEEP
excelvba
Matthew Baker
Matthew Baker

Reputation: 13

How to search/replace terms (and their formatting)?

I'm trying to search for various terms in a workbook and change their formatting to red font, bold (to highlight the terms, essentially). I found the below script, which works for a single term. I've been trying to add additional terms with no success. Any help would be appreciated. Thanks in advance! 

Sub colorText()

Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String
Dim endPos As Integer
Dim testPos As Integer

' specify text to search.
 searchText = "Trust"

' loop trough all cells in selection/range
For Each cl In Selection

  totalLen = Len(searchText)
  startPos = InStr(cl, searchText) 
  testPos = 0

  Do While startPos > testPos
    With cl.Characters(startPos, totalLen).Font
      .FontStyle = "Bold"
      .ColorIndex = 3
    End With

    endPos = startPos + totalLen
    testPos = testPos + endPos
    startPos = InStr(testPos, cl, searchText, vbTextCompare)
  Loop

Next cl

End Sub

Upvotes: 1

Views: 49

Answers (1)

41686d6564
41686d6564

Reputation: 19651

One way to handle this is to use ParamArray. Remove the searchText and add a ParamArray parameter to your Sub:

Sub ColorText(ParamArray searchStrings() As Variant)
    Dim cl As Range
    Dim startPos As Integer
    Dim totalLen As Integer
    Dim endPos As Integer
    Dim testPos As Integer

    For Each searchItem In searchStrings
        For Each cl In Selection
            totalLen = Len(searchItem)
            startPos = InStr(cl, searchItem)
            testPos = 0

            Do While startPos > testPos
                With cl.Characters(startPos, totalLen).Font
                    .FontStyle = "Bold"
                    .ColorIndex = 3
                End With

                endPos = startPos + totalLen
                testPos = testPos + endPos
                startPos = InStr(testPos, cl, searchItem, vbTextCompare)
            Loop
        Next cl
    Next searchItem
End Sub

And now you can call another Sub/Macro with multiple strings like this:

Sub Test()
    ColorText "Trust", "Foo", "Bar"
End Sub

Result:

Result

If you don't want to use ParamArray or a separate method (Sub), then you can run the For Each loop on an array of strings instead:

For Each searchItem In Array("Trust", "Foo", "Bar")
    ' Do your magic here.
Next searchItem

Upvotes: 2

Related Questions