基于Order_ID对比乱序工作表CaseNumber与Sentence Details的VBA求助

要解决两张行顺序不一致的工作表中，基于Order_ID对比CaseNumber和Sentence Details字段的问题，你需要抛弃原代码中按固定行号匹配的逻辑，改用基于唯一标识（Order_ID）的查找匹配。以下是修改后的VBA代码及说明：

Sub CompareAndHighlightByOrderID()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim orderDict As Object
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long
    Dim orderID As String
    Dim caseNum1 As Variant, caseNum2 As Variant
    Dim sentence1 As Variant, sentence2 As Variant
    
    ' 初始化工作表对象
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    Set orderDict = CreateObject("Scripting.Dictionary")
    
    ' 遍历Sheet2，将Order_ID作为键，CaseNumber和Sentence Details存入字典
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow2
        orderID = Trim(ws2.Cells(i, "A").Value)
        If orderID <> "" Then
            ' 用数组存储两个字段值
            orderDict(orderID) = Array(ws2.Cells(i, "B").Value, ws2.Cells(i, "D").Value)
        End If
    Next i
    
    ' 遍历Sheet1，根据Order_ID匹配Sheet2的数据并对比
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow1
        orderID = Trim(ws1.Cells(i, "A").Value)
        caseNum1 = ws1.Cells(i, "B").Value
        sentence1 = ws1.Cells(i, "D").Value
        
        ' 先清除原有底色
        ws1.Cells(i, "B").Interior.ColorIndex = xlColorIndexNone
        ws1.Cells(i, "D").Interior.ColorIndex = xlColorIndexNone
        
        If orderID <> "" And orderDict.Exists(orderID) Then
            ' 取出Sheet2对应的数据
            caseNum2 = orderDict(orderID)(0)
            sentence2 = orderDict(orderID)(1)
            
            ' 对比两个字段
            If caseNum1 = caseNum2 And sentence1 = sentence2 Then
                ' 匹配成功，标绿
                ws1.Cells(i, "B").Interior.Color = RGB(0, 255, 0)
                ws1.Cells(i, "D").Interior.Color = RGB(0, 255, 0)
            Else
                ' 不匹配，标红
                ws1.Cells(i, "B").Interior.Color = RGB(255, 0, 0)
                ws1.Cells(i, "D").Interior.Color = RGB(255, 0, 0)
            End If
        Else
            ' 未找到对应Order_ID，标黄提示
            ws1.Cells(i, "B").Interior.Color = RGB(255, 255, 0)
            ws1.Cells(i, "D").Interior.Color = RGB(255, 255, 0)
        End If
    Next i
    
    ' 可选：遍历Sheet2，标记未在Sheet1中出现的Order_ID对应字段
    For i = 2 To lastRow2
        orderID = Trim(ws2.Cells(i, "A").Value)
        ws2.Cells(i, "B").Interior.ColorIndex = xlColorIndexNone
        ws2.Cells(i, "D").Interior.ColorIndex = xlColorIndexNone
        
        If orderID <> "" And Not orderDict.Exists(orderID) Then
            ws2.Cells(i, "B").Interior.Color = RGB(255, 255, 0)
            ws2.Cells(i, "D").Interior.Color = RGB(255, 255, 0)
        End If
    Next i
    
    Set orderDict = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

关键修改说明： #

1. 使用字典存储映射关系：把Sheet2的Order_ID和对应的CaseNumber、Sentence Details存入字典，实现高效查找，避免逐行遍历的低效问题。
2. 基于Order_ID匹配而非行号：遍历Sheet1时，通过Order_ID从字典中取出Sheet2的对应数据，完全不受行顺序影响。
3. 新增未匹配提示：如果某条Order_ID在另一张表中不存在，用黄色高亮提示，避免遗漏数据。
4. 清除原有格式：对比前先清除单元格底色，避免旧格式干扰结果。

使用注意事项： #

• 确保Order_ID是唯一标识，如果存在重复的Order_ID，字典会只保留最后一条记录，这种情况需要额外处理重复值。
• 根据实际表名修改Sheet1和Sheet2的名称，确保列号对应正确（A=Order_ID，B=CaseNumber，D=Sentence Details）。

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