excel - 如何搜索/替换术语(及其格式)？

Question

我正在尝试在工作簿中搜索各种术语并将其格式更改为红色字体、粗体(本质上是为了突出显示这些术语)。我找到了以下脚本，它适用于单个术语。我一直在尝试添加其他条款，但没有成功。任何帮助，将不胜感激。提前致谢!

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

Answer 1

处理此问题的一种方法是使用 ParamArray .删除 searchText并添加 ParamArray 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

现在您可以使用多个字符串调用另一个子/宏，如下所示:

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

结果:



如果您不想使用 ParamArray或单独的方法 (Sub)，然后您可以运行 For Each而是在字符串数组上循环:

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