VBA 两个工作表做比较

计划部门用于对比两份明细表的不同，以免少下漏下任务

Sub sheetsdb()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim i As Long, j As Long 'i为行，j为列
    Dim rowcount As Long, colcount As Long, rowcount2 As Long
    
    
    MsgBox "用于对比的两个表需要有相同的结构，否则效果不好"
      
    ' 设置工作表1和工作表2的范围
    
    Set ws1 = ThisWorkbook.Sheets("原始表")
    Set ws2 = ThisWorkbook.Sheets("对比表")
    rowcount = ws1.Range("A" & Rows.Count).End(xlUp).Row
    colcount = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
    rowcount2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
   '所选范围的行数按两个表中最大的 
    If rowcount >= rowcount2 Then
        Set rng1 = ws1.Range(ws1.Cells(1, 1), ws1.Cells(rowcount, colcount))
        Set rng2 = ws2.Range(ws2.Cells(1, 1), ws2.Cells(rowcount, colcount))
    ElseIf rowcount < rowcount2 Then
        Set rng1 = ws1.Range(ws1.Cells(1, 1), ws1.Cells(rowcount2, colcount))
        Set rng2 = ws2.Range(ws2.Cells(1, 1), ws2.Cells(rowcount2, colcount))
    End If
    
    

   '清除底色
    rng1.Interior.Pattern = xlPatternNone
    rng2.Interior.Pattern = xlPatternNone
      
    ' 比较两个范围中的每个单元格，不同值标上底色。
    For i = 1 To rng1.Rows.Count
        For j = 1 To rng1.Columns.Count
            If rng1.Cells(i, j).Value <> rng2.Cells(i, j).Value Then
                rng1.Cells(i, j).Interior.Color = RGB(255, 0, 255) ' 将表1中不同值的单元格标记为紫色
                rng2.Cells(i, j).Interior.Color = RGB(0, 255, 255) ' 将表2中不同值的单元格标记为蓝色![在这里插入图片描述](https://img-blog.csdnimg.cn/90e74a81d0d441689ba147106988126e.png)

            End If
        Next j
    Next i
End Sub