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
| Option Explicit
Sub test()
Dim a, i As Long, dico As Object, txt As String, x As Range
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
With Sheets("Feuil1").Cells(1).CurrentRegion
a = .Value
For i = 2 To UBound(a, 1)
txt = Join$(WorksheetFunction.Index(a, i, 0), ",")
dico(txt) = ""
Next
End With
With Sheets("Feuil2").Cells(1).CurrentRegion
.Interior.ColorIndex = xlNone
a = .Value
For i = 2 To UBound(a, 1)
txt = Join$(WorksheetFunction.Index(a, i, 0), ",")
If dico.exists(txt) Then
If x Is Nothing Then
Set x = .Rows(i)
Else
Set x = Union(x, .Rows(i))
End If
End If
Next
If Not x Is Nothing Then x.Interior.ColorIndex = 43
End With
End Sub |
Partager