excel - 如何在 Excel 2010 中针对给定范围或选择突出显示同一单元格中单词的每个实例？

Question

我想在我的 Excel 工作表(使用 Excel 2010)的选定列中以红色和粗体突出显示单词/短语的每个实例。例如，如果列 A1:A10 包含句子“The brown fox likes the other brown fox”，我想突出显示此范围内“brown fox”的每个实例。

我找到了一个宏 here它仅突出显示每个单元格中“brown fox”的第一个实例:

Sub colorText()

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

' specify text to searh.
searchText = "brown fox"

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

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

  If startPos > 0 Then
    With cl.Characters(startPos, totalLen).Font
      .FontStyle = "Bold"
      .ColorIndex = 3
    End With
  End If
Next cl

End Sub

我想编辑此宏，使其突出显示“brown fox”的每个实例，而不仅仅是第一个。作为尝试，我尝试了以下操作:

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 = "brown fox"

' 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, searchText)
  Loop

Next cl

End Sub

但是，这仍然只是格式化“brown fox”的第一个实例。

任何想法/编辑将不胜感激。

Answer 1

你的错误在于你的逻辑。您应该更正以下代码:

 startPos = InStr(testPos, cl, searchText, vbTextCompare)

而不是这样做:

 startPos = InStr(testPos, searchText)

在第二个子。你现在看到了吗？ :-)