我想将工作表 1 的所有单元格与工作表 2 进行比较，匹配的值变为黄色

Question

我想将工作表 1 的所有单元格与工作表 2 进行比较，匹配的值变为黄色，按照我使用的代码，

Sub match()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
strRangeToCheck = "A1:IV65536"
varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet2").Range(strRangeToCheck)
Debug.Print Now
For icell = LBound(varSheetA, 1) To UBound(varSheetA, 1)
If varSheetA(icell) = varSheetB(icell) Then
cell.Interior.Color = vbYellow
`enter code here` End If
Next icell
End Sub

Answer 1

尝试

Sub match()
Dim shtA As Worksheet
Dim shtB As Worksheet
Dim strRangeToCheck As String
Dim cA as range, cB as range
strRangeToCheck = "A1:IV65536"
set shtA = Worksheets("Sheet1")
set shtB = Worksheets("Sheet2")
Debug.Print Now
For Each cA IN shtA.Range(strRangeToCheck)
on error resume next
if cA.value <> "" then
Set cB = shtB.Range(strRangeToCheck).find(what:=cA.value, lookin:=xlvalues, lookat:=xlwhole)
If Not cB is Nothing then
cA.Interior.Color = vbYellow
End If
end if
on error goto 0
Next cA
End Sub