EXCEL跨表比较两列,并填充新值背景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Sub FillNewToYellow()
    Dim dic
    Dim oldArr(), updatedArr()
   
    On Error Resume Next
    If Worksheets("old") Is Nothing Then
        MsgBox "Missing old sheet"
    ElseIf Worksheets("updated") Is Nothing Then
        MsgBox "Missing updated sheet"
    Else
        Set dic = CreateObject("Scripting.Dictionary")
        ActiveWorkbook.Sheets("old").Activate
        oldArr = Range("B1:B" & ActiveSheet.UsedRange.Rows.Count)
         
        For i = 1 To UBound(oldArr)
            dic(oldArr(i, 1)) = ""
        Next
         
        ActiveWorkbook.Sheets("updated").Activate
        updatedArr = Range("B1:B" & ActiveSheet.UsedRange.Rows.Count)
         
        For i = 1 To UBound(updatedArr)
            If dic.exists(updatedArr(i, 1)) = False Then
                Rows(i & ":" & i).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535 'Yellow
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            End If
        Next
    End If
End Sub

posted @   Yu  阅读(842)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示