VBA代码对比两工作簿账户列高亮唯一值失效排查求助

问题背景 #

需求为对比两个工作簿的「Accounts」列，高亮Workbook1中存在但Workbook2中不存在的账户值。已在WB1中加入测试值999999999，但该值未被高亮；代码在其他工作簿可正常运行，仅在指定两个工作簿失效。

失效代码与可用代码对比 #

失效代码 #

Sub CompareCols()  
    Application.ScreenUpdating = False  
    Dim Rng As Range, RngList As Object, WB1 As Worksheet, WB2 As Worksheet  
    Set WB1 = ThisWorkbook.Sheets("Detailed Bill Info")  
    Set WB2 = Workbooks("FDG Accounts.xlsx").Sheets("FDG Accounts")  
    Set RngList = CreateObject("Scripting.Dictionary")  
    For Each Rng In WB2.Range("A2", WB2.Range("A" & Rows.Count).End(xlUp))  
        If Not RngList.Exists(Rng.Value) Then  
            RngList.Add Rng.Value, Nothing  
        End If  
    Next  
    For Each Rng In WB1.Range("A2", WB1.Range("A" & Rows.Count).End(xlUp))  
        If RngList.Exists(Rng.Value) Then  
            WB1.Cells(Rng.Row, 8).Interior.ColorIndex = 6  
        End If  
    Next  
    Application.ScreenUpdating = True  
End Sub

可用代码 #

Sub CompareCols()
'Disabling the screen updating.
Application.ScreenUpdating = False

'Declaring variables
Dim Rng As Range, RngList As Object, WB1 As Worksheet, WB2 As Worksheet

'Setting values to variables declared
Set WB1 = ThisWorkbook.Sheets("FDG Accounts")
Set WB2 = Workbooks("Client Bill Info.xlsm").Sheets("Detailed Bill Info")
Set RngList = CreateObject("Scripting.Dictionary")

'Loop to collect values that are in column A of this workbook
'that are not in column A of WB2
For Each Rng In WB2.Range("A2", WB2.Range("A" & Rows.Count).End(xlUp))
    If Not RngList.Exists(CStr(Rng.Value)) Then
        RngList.Add CStr(Rng.Value), Nothing
    End If
Next
'Iterate through results and highlight the cell with the unique value
For Each Rng In WB1.Range("A2", WB1.Range("A" & Rows.Count).End(xlUp))
    If Not RngList.Exists(CStr(Rng.Value)) Then
        WB1.Cells(Rng.Row, 1).Interior.ColorIndex = 6
        tmpStr = Rng.Value
        'MsgBox (tmpStr)
    End If
Next
Sheets.Add.Name = "myNewSheet"

Application.ScreenUpdating = True
End Sub

失效原因总结 #

1. 数据类型不匹配：失效代码直接使用单元格原始值Rng.Value操作字典，若两个工作簿中账户列的数据类型不一致（如一个是数字格式、一个是文本格式的数字），字典的键值匹配会失效。可用代码通过CStr()强制统一为字符串类型，避免了类型差异导致的匹配错误。
2. 逻辑判断完全反向：需求是高亮WB1独有的账户，失效代码中If RngList.Exists(Rng.Value)的逻辑是标记WB1与WB2共有的账户，和需求完全相反；可用代码用If Not RngList.Exists(...)才是正确的判断逻辑。
3. 高亮列错误：失效代码高亮的是第8列（H列），而非账户所在的第1列（A列），不符合“高亮账户值”的需求。
4. 工作簿/工作表绑定错误：失效代码中WB1和WB2的绑定对象与需求不符，导致遍历和判断的数据源完全错误，直接偏离目标。

修正后的代码示例 #

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, WB1 As Worksheet, WB2 As Worksheet
    
    ' 根据实际需求绑定正确的工作簿和工作表
    Set WB1 = ThisWorkbook.Sheets("Detailed Bill Info") ' 要高亮的目标表
    Set WB2 = Workbooks("FDG Accounts.xlsx").Sheets("FDG Accounts") ' 对比表
    Set RngList = CreateObject("Scripting.Dictionary")
    
    ' 存入WB2的所有账户（统一为字符串）
    For Each Rng In WB2.Range("A2", WB2.Range("A" & Rows.Count).End(xlUp))
        If Not RngList.Exists(CStr(Rng.Value)) Then
            RngList.Add CStr(Rng.Value), Nothing
        End If
    Next
    
    ' 遍历WB1，高亮不存在于WB2的账户（A列）
    For Each Rng In WB1.Range("A2", WB1.Range("A" & Rows.Count).End(xlUp))
        If Not RngList.Exists(CStr(Rng.Value)) Then
            Rng.Interior.ColorIndex = 6 ' 直接高亮当前单元格，避免列号错误
        End If
    Next
    
    Application.ScreenUpdating = True
End Sub

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