基于VBA开发Excel跨文件单元格对比与自动插入高亮宏需求

嘿，我来帮你搞定这个VBA自动化的需求！手动核对确实太费时间了，下面这个宏应该能完美解决你的问题——它会自动对比日报表和主文件里的Code Number，把主文件没有的记录插进去，还会在日报表里高亮那些新增的记录。

实现思路 #

• 让用户手动选择主文件和日报表（比硬编码路径更灵活，适配不同文件位置）
• 用字典存储主文件的所有Code Number，实现快速查找（数据量大时比嵌套循环效率高很多）
• 遍历日报表每一行，检查Code Number是否存在于主文件
• 若不存在，则将整行记录插入主文件末尾，并给日报表对应行设置高亮标记
• 操作完成后给出提示，可选自动保存/关闭文件

完整VBA代码 #

Sub CompareAndInsertRecords()
    Dim mainWB As Workbook, dailyWB As Workbook
    Dim mainWS As Worksheet, dailyWS As Worksheet
    Dim codeDict As Object
    Dim lastRowMain As Long, lastRowDaily As Long
    Dim i As Long, j As Long
    Dim codeNumber As String
    
    ' 创建字典用于快速存储和查找主文件的Code Number
    Set codeDict = CreateObject("Scripting.Dictionary")
    
    ' 弹出对话框让用户选择主文件和日报表
    Set mainWB = Workbooks.Open(Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx;*.xls)", Title:="选择主文件"))
    Set dailyWB = Workbooks.Open(Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx;*.xls)", Title:="选择日报表文件"))
    
    ' 假设数据在第一个工作表，可根据实际修改工作表名称或索引
    Set mainWS = mainWB.Sheets(1)
    Set dailyWS = dailyWB.Sheets(1)
    
    ' 获取主文件和日报表的最后数据行号（假设Code Number在A列）
    lastRowMain = mainWS.Cells(mainWS.Rows.Count, "A").End(xlUp).Row
    lastRowDaily = dailyWS.Cells(dailyWS.Rows.Count, "A").End(xlUp).Row
    
    ' 将主文件所有Code Number存入字典（跳过表头，从第二行开始）
    For i = 2 To lastRowMain
        codeNumber = Trim(mainWS.Cells(i, "A").Value)
        If Not codeDict.Exists(codeNumber) Then
            codeDict.Add codeNumber, i ' 存储行号，方便后续追溯（可选）
        End If
    Next i
    
    ' 遍历日报表，对比并插入新记录
    For i = 2 To lastRowDaily
        codeNumber = Trim(dailyWS.Cells(i, "A").Value)
        ' 检查当前Code Number是否在主文件中存在
        If Not codeDict.Exists(codeNumber) Then
            ' 获取主文件新的最后一行，插入记录
            lastRowMain = lastRowMain + 1
            ' 复制日报表当前行的4个字段（A到D列：Code Number、Code Type、Reason、Date work start）
            For j = 1 To 4
                mainWS.Cells(lastRowMain, j).Value = dailyWS.Cells(i, j).Value
            Next j
            ' 高亮日报表中新增的记录（黄色填充）
            dailyWS.Rows(i).Interior.Color = RGB(255, 255, 0)
            ' 将新增的Code Number加入字典，避免重复处理
            codeDict.Add codeNumber, lastRowMain
        End If
    Next i
    
    ' 提示操作完成，显示新增记录数量
    MsgBox "对比和插入操作完成！主文件新增了" & (codeDict.Count - (lastRowMain - (lastRowMain - (codeDict.Count - (lastRowMain - (lastRowMain - (codeDict.Count - (lastRowMain - 1)))))))) & "条记录，日报表中已高亮标记。"
    
    ' 可选：自动保存并关闭文件，取消注释即可
    ' mainWB.Save
    ' mainWB.Close SaveChanges:=True
    ' dailyWB.Close SaveChanges:=True
    
    ' 释放对象，避免内存泄漏
    Set codeDict = Nothing
    Set mainWS = Nothing
    Set dailyWS = Nothing
    Set mainWB = Nothing
    Set dailyWB = Nothing
End Sub

关键细节调整提示 #

• 列位置适配：如果你的Code Number不在A列，或者字段数量/位置不同，修改代码中对应的列索引（比如把"A"改成"C"，j=1 To 4改成对应字段的列数）
• 表头处理：如果文件没有表头，把遍历起始行i=2改成i=1
• 高亮颜色：可以替换RGB(255,255,0)为其他颜色值，比如橙色RGB(255,192,0)、浅蓝色RGB(189,215,238)
• 字典引用：如果运行时提示“找不到Scripting.Dictionary”，可以在VBA编辑器中通过工具>引用勾选“Microsoft Scripting Runtime”

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