Excel 突出显示句子中的特定文本

Question

我正在尝试突出显示句子中与另一列中的单词匹配的文本。假设不同行 B 的 A 列（包含“ball”、“can”）和不同行的 B 列（包含“He kicks a ball”、“He can eat that”）。

我只需要突出显示 B 列句子中与 A 列中定义的单词相匹配的单词，而不是整个句子。这可行吗？如何做到？

Answer 1

我发现自己也在问同样的问题，也许这对其他人来说仍然有意义。如何在 Excel 中执行代码：

1. 开发者工具
2. Visual Basic
3. 插入模块
4. 将代码粘贴到模块窗口中
5. 调整
   • comp1 = 源词的列号
   • comp2 = 包含要突出显示的单词的句子的列数
   • 长度 = 要比较的行数
6. 执行代码（F5）

代码：

Sub highlight()
Dim counter As Integer
Dim counter2 As Integer
Dim counter1 As Integer
Dim textmarker As Integer
Dim length As Integer
Dim start As Integer
Dim comp1 As Integer
Dim comp2 As Integer

start = 0
comp1 = 1
comp2 = 2
length = 3

For counter = 1 To length
    For counter2 = 1 To Len(Cells(counter, comp2))
        For counter1 = 1 To Len(Cells(counter, comp1))
            If Cells(counter, comp2).Characters(counter2 + counter1 - 1, 1).Text <> Cells(counter, comp1).Characters(counter1, 1).Text Then
                start = 0
                Exit For
            End If
            If Cells(counter, comp2).Characters(counter2 + counter1 - 1, 1).Text = Cells(counter, comp1).Characters(counter1, 1).Text And start = 0 Then
                start = counter2
                tempCouner = tempCounter + 1
            End If
            Next
            If start <> 0 Then
                For textmarker = start To start + Len(Cells(counter, comp1)) - 1
                Cells(counter, comp2).Characters(textmarker, 1).Font.ColorIndex = 3
                Next
            End If
            start = 0
        Next
    Next
End Sub