VBA第二种方法实现对比

最新推荐文章于 2023-10-26 09:37:51 发布

小何小何h

最新推荐文章于 2023-10-26 09:37:51 发布

阅读量974

点赞数 1

文章标签： java

本文链接：https://blog.csdn.net/weixin_53754024/article/details/130232469

版权

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