VBA比较两张表和两列并检查差异

Sub CompareTables()
    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim lastRowA As Long
    Dim lastRowB As Long
    Dim i As Long
    
    ' 设置表A和表B的工作表对象
    Set wsA = ThisWorkbook.Worksheets("表A")
    Set wsB = ThisWorkbook.Worksheets("表B")
    
    ' 获取表A和表B的最后一行
    lastRowA = wsA.Cells(wsA.Rows.Count, "A").End(xlUp).Row
    lastRowB = wsB.Cells(wsB.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历表A的每一行
    For i = 2 To lastRowA ' 假设第一行是表头，从第二行开始比较
        ' 获取当前行的唯一标识
        Dim id As String
        id = wsA.Cells(i, "A").Value
        
        ' 在表B中查找相同的唯一标识
        Dim matchRow As Range
        Set matchRow = wsB.Range("A2:A" & lastRowB).Find(What:=id, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' 如果在表B中找到了相同的唯一标识
        If Not matchRow Is Nothing Then
            ' 比较其他列的值
            Dim j As Long
            For j = 2 To wsA.Columns.Count ' 假设第一列是唯一标识，从第二列开始比较
                ' 获取当前列的值
                Dim valueA As Variant
                Dim valueB As Variant
                valueA = wsA.Cells(i, j).Value
                valueB = wsB.Cells(matchRow.Row, j).Value
                
                ' 比较两个值是否相等
                If valueA <> valueB Then
                    ' 将差异标记为红色
                    wsA.Cells(i, j).Interior.Color = RGB(255, 0, 0)
                    wsB.Cells(matchRow.Row, j).Interior.Color = RGB(255, 0, 0)
                End If
            Next j
        Else ' 如果在表B中没有找到相同的唯一标识
            ' 将整行标记为红色
            wsA.Rows(i).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub