Sub check_Click()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r1 As Range, r2 As Range
Dim cell1 As Range, cell2 As Range
Dim diffCount As Long
Dim sortColumn As Long
' 设置要比较的两个工作表
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
' 设置要排序的列,这里假设是第一列
sortColumn = 1
' 对两个工作表根据指定列进行排序
SortWorksheet ws1, sortColumn
SortWorksheet ws2, sortColumn
' 设置要比较的范围(这里假设两个工作表的范围相同,需要根据实际情况调整)
Set r1 = ws1.UsedRange
Set r2 = ws2.UsedRange
' 初始化差异计数器
diffCount = 0
' 比较两个工作表中的单元格
For Each cell1 In r1
Set cell2 = r2.Cells(cell1.Row, cell1.Column)
' 如果单元格内容不同,用颜色标记
If cell1.Value <> cell2.Value Then
cell1.Interior.Color = RGB(255, 50, 0)
cell2.Interior.Color = RGB(255, 50, 0)
diffCount = diffCount + 1
Else
cell1.Interior.Color = xlNone
cell2.Interior.Color = xlNone
End If
Next cell1
' 显示差异总数
MsgBox diffCount & " differences found.", vbInformation
End Sub
Sub SortWorksheet(ws As Worksheet, sortColumn As Long)
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Cells(1, sortColumn), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1").CurrentRegion
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub