vba - 如何比较两个不同工作表上的两个不同列并突出显示差异？

Question

我希望能够比较两个工作表，如果 ws1 上的 H 列和 ws2 上的 F 列中的日期存在差异，则突出显示 ws2 上的单元格。我遇到的问题是它们是两个不同的列，从两个不同的单元格开始(比较 ws1 上的 H9+ 和 ws2 上的 F10+) 这没有给我任何错误，但似乎什么也没发生。这是我到目前为止所拥有的:

Sub matchMe()
Dim wS As Worksheet, wT As Worksheet
Dim r1 As Range, r2 As Range
Dim cel1 As Range, cel2 As Range

Set wS = ThisWorkbook.Worksheets("Project Status Report L3")
Set wT = ThisWorkbook.Worksheets("Demand Mapping - Active")

With wS
    Set r1 = .Range("H9", .Cells(.Rows.Count, .Columns("R:R").Column).End(xlUp))
End With

With wT
    Set r2 = .Range("F10", .Cells(.Rows.Count, .Columns("G:G").Column).End(xlUp))
End With

On Error Resume Next
For Each cel1 In r1
    With Application
        Set cel2 = .Index(r2, .Match(cel1.Value, r2, 0)) 'find match in sheet2
        If Err = 0 Then
            If cel1.Offset(, 8) <> cel2.Offset(, 8) Then cel2.Interior.ColorIndex = 1  'if difference, color
        End If
        Err.Clear
    End With
Next cel1

结束子

Answer 1

不久前我在网上找到了这段代码，它应该可以满足您的需求。只需将 shtBefore 和 shtAfter 设置为工作表名称。

Sub compareSheets(shtBefore As String, shtAfter As String)

Dim mycell As Range
Dim mydiffs As Integer

For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then

        mycell.Interior.Color = vbYellow
        mydiffs = mydiffs + 1

    End If
Next

MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtAfter).Select

End Sub