Excel 2016新旧工作表行对比：替代条件格式的高效方案

我明白你之前手动用条件格式对比的痛苦——确实效率太低了！针对你提到的场景（OLD.xls和UPDATED.xls布局一致、A列为唯一ID、仅需匹配已有ID的行并高亮UPDATED中的差异单元格、忽略UPDATED的新行），这里有两个更高效的方案：

方法1：优化版条件格式（无需代码） #

这个方法比你之前的操作更简洁，只需设置一次条件格式就能自动高亮所有差异：

1. 同时打开OLD.xls和UPDATED.xls，切换到UPDATED的工作表（假设两个文件的工作表都叫Sheet1）。
2. 选中UPDATED中需要对比的区域：B2:E（从第2行开始的B到E列，可拖选到数据最后一行）。
3. 点击「开始」选项卡 → 「条件格式」→ 「新建规则」→ 选择「使用公式确定要设置格式的单元格」。
4. 在公式框中粘贴以下内容：

   =AND(ISNUMBER(MATCH($A2,[OLD.xls]Sheet1!$A:$A,0)),B2<>INDEX([OLD.xls]Sheet1!$B:$E,MATCH($A2,[OLD.xls]Sheet1!$A:$A,0),COLUMN()-1))
   

   公式解释：
   • ISNUMBER(MATCH($A2,[OLD.xls]Sheet1!$A:$A,0))：确认当前行的ID在OLD文件中存在（自动忽略UPDATED的新行）。
   • B2<>INDEX([OLD.xls]Sheet1!$B:$E,MATCH($A2,[OLD.xls]Sheet1!$A:$A,0),COLUMN()-1)：对比当前单元格与OLD中对应ID行的同列值，若不一致则触发格式。
5. 点击「格式」按钮，设置你想要的高亮样式（比如填充黄色），然后确认保存规则。

完成后，UPDATED中所有与OLD对应ID行有差异的单元格都会自动高亮，新行则不会被标记。

方法2：VBA脚本（一键自动化） #

如果你的数据量很大，或者需要频繁做这类对比，VBA脚本会更省心——运行一次就能自动完成所有对比和高亮：

1. 打开UPDATED.xls，按下Alt + F11打开VBA编辑器。
2. 右键点击左侧的工作簿名称 → 「插入」→ 「模块」。
3. 在弹出的代码窗口中粘贴以下代码：

   Sub HighlightDifferences()
       Dim wsOld As Worksheet, wsUpdated As Worksheet
       Dim rngOldIDs As Range, rngUpdatedIDs As Range
       Dim cell As Range, matchCell As Range
       Dim i As Integer
       
       ' 替换为你的工作表名称，如果不是Sheet1请修改
       Set wsOld = Workbooks("OLD.xls").Worksheets("Sheet1")
       Set wsUpdated = ThisWorkbook.Worksheets("Sheet1")
       
       ' 获取两个文件的ID数据范围（从第2行到最后一行）
       Set rngOldIDs = wsOld.Range("A2:A" & wsOld.Cells(wsOld.Rows.Count, "A").End(xlUp).Row)
       Set rngUpdatedIDs = wsUpdated.Range("A2:A" & wsUpdated.Cells(wsUpdated.Rows.Count, "A").End(xlUp).Row)
       
       ' 清除之前的高亮格式，避免重复标记
       wsUpdated.Range("B2:E" & wsUpdated.Cells(wsUpdated.Rows.Count, "A").End(xlUp).Row).ClearFormats
       
       ' 遍历UPDATED的每一行ID
       For Each cell In rngUpdatedIDs
           ' 在OLD中精确匹配当前ID
           Set matchCell = rngOldIDs.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
           If Not matchCell Is Nothing Then
               ' 对比B-E列的每个单元格
               For i = 2 To 5 ' B列是第2列，E列是第5列
                   If wsUpdated.Cells(cell.Row, i).Value <> wsOld.Cells(matchCell.Row, i).Value Then
                       ' 设置黄色高亮，可根据需求修改颜色代码
                       wsUpdated.Cells(cell.Row, i).Interior.Color = RGB(255, 255, 0)
                   End If
               Next i
           End If
       Next cell
       
       MsgBox "差异高亮完成！", vbInformation
   End Sub
   

4. 点击工具栏的「运行」按钮（绿色三角），或者按下F5执行脚本。

注意事项： #

• 确保OLD.xls和UPDATED.xls都处于打开状态。
• 如果你的工作表名称不是Sheet1，请修改代码中Worksheets("Sheet1")的部分。
• 脚本会先清除之前的高亮格式，避免重复标记。

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