☰

比较单元格和颜色匹配的单词

tag-icon • tag-icon microsoft-excel
比较单元格和颜色匹配的单词

我在 A 列中有句子，在 B 列中有句子。我想匹配 A1 和 B1，并将匹配的单词涂成红色。例如：

A1: Lenovo T450 with 5 GB RAM, Intel i5 CPU, 500 GB HDD, 14" HD screen, weight 3.5 pounds

B1: 5 GB  i5 CPU, 500 GB HDD, 14" HD 3.5

然后我想将与 B1 匹配的单词涂成红色。

答案1

9 月 2 日重制，无用代码被废弃

您可以尝试以下操作：

Sub sameStringRed()

Dim i As Integer, j As Integer, intStart As Integer
Dim rngA As Range, rngB As Range
Dim strDelimit As String: strDelimit = " "

For Each rngA In Selection.Rows
    Set rngB = rngA.Offset(0, 1)
    On Error GoTo Error
    strA = Split(rngA.Text, strDelimit)
    strB = Split(rngB.Text, strDelimit)
    For j = LBound(strA) To UBound(strA)
        For i = LBound(strB) To UBound(strB)
            If strA(j) = strB(i) Then
                intStart = InStr(1, UCase(rngA.Value), UCase(strB(i)))
                While intStart > 0
                    If Mid(rngA, intStart + Len(strB(i)), 1) = strDelimit Or Mid(rngA, intStart + Len(strB(i)), 1) = "" Then
                        rngA.Characters(Start:=intStart, Length:=Len(strB(i))).Font.ColorIndex = 3
                    End If
                    intStart = InStr(intStart + 1, UCase(rngA.Value), UCase(strB(i)))
                Wend
            End If
        Next i
    Next j
Next
Exit Sub
Error:
MsgBox "Please do not select multiple columns"
End Sub

相关内容