我试图比较A-B列并突出显示相同的文本,但使用上述公式时,有些文本根本不匹配。因此,我使用了表单(
VBA宏来比较两个列并突出显示单元格差异)代码,并修改了一些内容以适应我的应用程序,并查找任何所需的列(只需点击它)。在我的情况下,我在每个列上使用大量且不同的行。希望这可以帮助您:
子ABTextCompare()
Dim Report As Worksheet
Dim i, j, colNum, vMatch As Integer
Dim lastRowA, lastRowB, lastRow, lastColumn As Integer
Dim ColumnUsage As String
Dim colA, colB, colC As String
Dim A, B, C As Variant
Set Report = Excel.ActiveSheet
vMatch = 1
'Select A and B Columns to compare
On Error Resume Next
Set A = Application.InputBox(Prompt:="Select column to compare", Title:="Column A", Type:=8)
If A Is Nothing Then Exit Sub
colA = Split(A(1).Address(1, 0), "$")(0)
Set B = Application.InputBox(Prompt:="Select column being searched", Title:="Column B", Type:=8)
If A Is Nothing Then Exit Sub
colB = Split(B(1).Address(1, 0), "$")(0)
'Select Column to show results
Set C = Application.InputBox("Select column to show results", "Results", Type:=8)
If C Is Nothing Then Exit Sub
colC = Split(C(1).Address(1, 0), "$")(0)
'Get Last Row
lastRowA = Report.Cells.Find("", Range(colA & 1), xlFormulas, xlByRows, xlPrevious).Row - 1 ' Last row in column A
lastRowB = Report.Cells.Find("", Range(colB & 1), xlFormulas, xlByRows, xlPrevious).Row - 1 ' Last row in column B
Application.ScreenUpdating = False
'***************************************************
For i = 2 To lastRowA
For j = 2 To lastRowB
If Report.Cells(i, A.Column).Value <> "" Then
If InStr(1, Report.Cells(j, B.Column).Value, Report.Cells(i, A.Column).Value, vbTextCompare) > 0 Then
vMatch = vMatch + 1
Report.Cells(i, A.Column).Interior.ColorIndex = 35 'Light green background
Range(colC & 1).Value = "Items Found"
Report.Cells(i, A.Column).Copy Destination:=Range(colC & vMatch)
Exit For
Else
'Do Nothing
End If
End If
Next j
Next i
If vMatch = 1 Then
MsgBox Prompt:="No Itmes Found", Buttons:=vbInformation
End If
'***************************************************
Application.ScreenUpdating = True
End Sub