请先确认两个表的格式相同,并且有相同的主键列,以下是使用VBA比较和标记的代码:
```VBA
Sub CompareAndMark()
Dim firstSheet As Worksheet
Dim secondSheet As Worksheet
Dim firstLastRow As Long
Dim secondLastRow As Long
Dim firstKey As Range
Dim secondKey As Range
Dim i As Long, j As Long
'选择要比较的两个工作表
Set firstSheet = Application.Worksheets("Sheet1")
Set secondSheet = Application.Worksheets("Sheet2")
'获取每一个工作表的最后一行
firstLastRow = firstSheet.Cells(Rows.Count, "A").End(xlUp).Row
secondLastRow = secondSheet.Cells(Rows.Count, "A").End(xlUp).Row
'获取主键列的范围,将主键用虚线框进行标记
Set firstKey = firstSheet.Range("A2:A" & firstLastRow)
firstSheet.Range("A1").Borders(xlEdgeLeft).LineStyle = xlDot
firstSheet.Range("A1").Borders(xlEdgeTop).LineStyle = xlDot
firstSheet.Range("A1").Borders(xlEdgeBottom).LineStyle = xlDot
firstKey.Borders(xlEdgeLeft).LineStyle = xlDot
firstKey.Borders(xlEdgeBottom).LineStyle = xlDot
Set secondKey = secondSheet.Range("A2:A" & secondLastRow)
secondSheet.Range("A1").Borders(xlEdgeLeft).LineStyle = xlDot
secondSheet.Range("A1").Borders(xlEdgeTop).LineStyle = xlDot
secondSheet.Range("A1").Borders(xlEdgeBottom).LineStyle = xlDot
secondKey.Borders(xlEdgeLeft).LineStyle = xlDot
secondKey.Borders(xlEdgeBottom).LineStyle = xlDot
'按主键列排序
firstSheet.Sort.SortFields.Clear
firstSheet.Sort.SortFields.Add Key:=firstKey, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
firstSheet.Sort.Apply
secondSheet.Sort.SortFields.Clear
secondSheet.Sort.SortFields.Add Key:=secondKey, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
secondSheet.Sort.Apply
'比较并标记不同处
For i = 2 To firstLastRow
For j = 2 To secondLastRow
If firstSheet.Cells(i, "A").Value = secondSheet.Cells(j, "A").Value Then
For k = 2 To firstSheet.UsedRange.Columns.Count
If firstSheet.Cells(i, k).Value <> secondSheet.Cells(j, k).Value Then
firstSheet.Cells(i, k).Interior.Color = vbYellow
secondSheet.Cells(j, k).Interior.Color = vbYellow
End If
Next k
Exit For
End If
Next j
Next i
End Sub
```
上述代码中,我们首先定义了两个工作表(firstSheet和secondSheet),然后分别获取两个表的最后一行(firstLastRow和secondLastRow),并为主键列设置了虚线边框。
接着,我们连接循环遍历第一个表中从第二行开始到最后一行,然后遍历第二个表中从第二行开始到最后一行,逐一比较两个表中的每个单元格。如果发现不同之处,则使用Range.Interior.Color属性设置所在单元格的颜色为黄色。
您可以将上述代码复制到VBA编辑器中,并测试它是否可以为两个表中不同的单元格标记出颜色。