|
本帖最后由 AVEL 于 2012-9-4 17:31 编辑
- Sub 检查()
- Dim d As Object, i&
- Dim ar, br, rng2 As Range, rng3 As Range
- ar = Sheet1.Range("b1:c" & Sheet1.Range("b65536").End(xlUp).Row)
- br = Sheet2.Range("b1:c" & Sheet2.Range("b65536").End(xlUp).Row)
- Set d = CreateObject("scripting.dictionary")
- For i = 7 To UBound(ar)
- d(ar(i, 2)) = ar(i, 1)
- d(ar(i, 1)) = ar(i, 2)
- Next
- For i = 3 To UBound(br)
- If Not d.exists(br(i, 1)) And Not d.exists(br(i, 2)) Then
- If rng2 Is Nothing Then Set rng2 = Sheet2.Range("b" & i & ":c" & i) Else Set rng2 = Union(rng2, Sheet2.Range("b" & i & ":c" & i))
- Else
- If br(i, 1) <> d(br(i, 2)) Or br(i, 2) <> d(br(i, 1)) Then
- If rng3 Is Nothing Then Set rng3 = Sheet2.Range("b" & i & ":c" & i) Else Set rng3 = Union(rng3, Sheet2.Range("b" & i & ":c" & i))
- End If
- End If
- Next
- Sheet2.Cells.Interior.ColorIndex = xlNone
- If Not rng3 Is Nothing Then rng3.Interior.Color = vbRed
- If Not rng2 Is Nothing Then rng2.Interior.Color = vbYellow
- ' For i = 7 To Sheet1.Range("b56565").End(3).Row
- ' If Sheet1.Cells(i, 2) <> "" Then Sheet1.Cells(i, 1) = i - 6
- ' Next
- If rng2 Is Nothing Then Exit Sub
- With Sheet1
- Dim rng As Range
- rng2.Copy .[b65536].End(3).Offset(1)
- Set rng = .[a65536].End(3).Offset(1).Resize(.[b65536].End(3).Row - .[a65536].End(3).Row)
- rng.Formula = "=row()-6"
- rng.Value = rng.Value
- End With
- End Sub
复制代码 改写了一下 |
|