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
| Sub Test_doublons()
Dim DL As Long
With Sheets("Feuil1") 'à adapter bonne feuille
DL = .Range("A" & .Rows.Count).End(xlUp).Row ' à adapter à la bonne colonne
IdentifieDoublons Range("A2:A" & DL)
End With
End Sub
Sub IdentifieDoublons(Plg As Range)
Dim Un As Collection, cel As Range, liste, x As Long
Set Un = New Collection
With Sheets("Feuil1") 'à adapter bonne feuille
x = .Range("D" & .Rows.Count).End(xlUp).Row
liste = .Range("D2", "D" & x) 'liste définie en D
For Each cel In Plg
On Error Resume Next
Un.Add cel, CStr(cel)
If Err <> 0 Then
On Error GoTo 0
For x = 1 To UBound(liste, 1)
If cel = liste(x, 1) Then
cel.Interior.ColorIndex = 7
Exit For
Else
cel.Interior.ColorIndex = 2
End If
Next x
End If
Next cel
End With
Set Un = Nothing
End Sub |
Partager