Sub test2()
Dim arr() As String
Dim arr1() As String
Dim n As Long
Dim n1 As Long
Dim i As Long
Dim index As Long '记录下标
n = Application.WorksheetFunction.CountA(Worksheets(1).Range("B:B")) '获得sheet1B列非空数据数量
n1 = Application.WorksheetFunction.CountA(Worksheets(2).Range("B:B")) '获得sheet2B列非空数据数量
ReDim arr(1 To n) As String
ReDim arr1(1 To n1) As String
For i = 1 To n Step 1
arr(i) = Worksheets(1).Cells(i, 2) 'sheet1动态数组
Next i
For i = 1 To n1 Step 1
arr1(i) = Worksheets(2).Cells(i, 2) 'sheet2动态数组
Next i
For i = 1 To n Step 1
index = 1
Do While index < n1 And arr(i) <> arr1(index) '下标小于n1 并且内容不相同
index = index + 1 '找到记录下标
Loop
MsgBox index
If arr(i) <> arr1(index) Then '判断不相等
Worksheets(1).Cells(i, 2).Interior.Color = RGB(82, 191, 197)
ElseIf arr(i) = arr1(index) Then '相等
Worksheets(1).Cells(i, 2).Interior.Color = RGB(139, 190, 221)
End If
Next i
End Sub
01-22
743
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
04-18
1092
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)