|
本帖最后由 cunfu2010 于 2016-5-13 10:00 编辑
表1与表2比对。如何实现:表1与表2所有完全相同的数据行自动将字体变成白色(即实现隐藏效果)并隐藏行,剩下两表比对后数据有差异的(比如说表2比表1多出来的1人的记录,或者同一人的某项社保数据有出入)记录行,用红色标记出来的。
- Sub Macro1()
- Dim arr, brr, ar, d, d2, i&, j%
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- brr = Sheet2.Range("a1").CurrentRegion
- ReDim ar(1 To UBound(arr))
- For i = 2 To UBound(arr)
- p = ""
- For j = 1 To UBound(arr, 2)
- p = p & "," & arr(i, j)
- Next
- ar(i) = p
- d(p) = i
- Next
- Application.ScreenUpdating = False
- For i = 2 To UBound(brr)
- p = ""
- For j = 1 To UBound(brr, 2)
- p = p & "," & brr(i, j)
- Next
- d2(p) = i
- If Not d.Exists(p) Then
- Sheet2.Cells(i, 1).Resize(1, UBound(brr, 2)).Font.ColorIndex = 3
- Else
- Sheet2.Rows(i).Hidden = True
- End If
- Next
- For i = 2 To UBound(ar)
- If Not d2.Exists(ar(i)) Then
- Sheet1.Cells(i, 1).Resize(1, UBound(arr, 2)).Font.ColorIndex = 3
- Else
- Sheet1.Rows(i).Hidden = True
- End If
- Next
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|