同工作簿双工作表对比高亮差异及VBA报错'438'问题咨询

报错438根因 #

• 你存入字典的是单元格Range对象而非单元格实际值，执行If sMetric = dict(sKey) Then时基础类型和对象无法直接比较，触发属性/方法不支持报错
• 未做键存在性校验，调用字典中不存在的sKey也会触发该类报错

修复实现逻辑 #

1. 构造键时使用行号+列号的唯一组合，保证两个工作表的同位置单元格对应同一个键
2. 字典存储值时显式读取单元格的Value属性，不直接存储Range对象
3. 对比时先判断键是否存在，再执行值比较，差异单元格可直接高亮，也可保留你加末尾空格的逻辑配合条件格式使用

修正后的代码示例 #

Sub 对比两工作表标记差异()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Dim sht1 As Worksheet, sht2 As Worksheet
    Dim i As Long, j As Long, sKey As String
    
    ' 替换为你实际的两个工作表名称
    Set sht1 = ThisWorkbook.Sheets("Sheet1")
    Set sht2 = ThisWorkbook.Sheets("Sheet2")
    
    ' 先将第一个工作表的内容存入字典，键为行|列，值为单元格值
    For i = 1 To sht1.UsedRange.Rows.Count
        For j = 1 To sht1.UsedRange.Columns.Count
            sKey = i & "|" & j
            dict(sKey) = sht1.Cells(i, j).Value
        Next j
    Next i
    
    ' 遍历第二个工作表对比
    For i = 1 To sht2.UsedRange.Rows.Count
        For j = 1 To sht2.UsedRange.Columns.Count
            sKey = i & "|" & j
            ' 先判断键是否存在，再对比值
            If dict.Exists(sKey) Then
                If sht2.Cells(i, j).Value <> dict(sKey) Then
                    ' 直接高亮差异单元格，无需后续条件格式
                    sht2.Cells(i, j).Interior.Color = vbRed
                    ' 如果要保留原有加空格的逻辑，替换上面一行为：
                    ' sht2.Cells(i, j).Value = sht2.Cells(i, j).Value & " "
                End If
            Else
                ' 第一个表不存在该位置单元格，直接标记差异
                sht2.Cells(i, j).Interior.Color = vbRed
            End If
        Next j
    Next i
    
    Set dict = Nothing
End Sub

额外优化提示 #

• 如果工作表数据量较大，可先将两个表的内容读入数组再处理，比逐单元格读取性能提升10倍以上
• 两个表已用区域大小不一致时，可先取最大行号、最大列号作为循环边界，避免漏对比超出小表范围的单元格

内容的提问来源于stack exchange，提问作者Arun