Sub Mes_Communes()
Dim a, i As Long, j As Long, n As Long
Sheets("Feuil3").Cells.Clear
With Sheets("Feuil2")
a = .Range("b1", .Range("b" & Rows.Count).End(xlUp)).Value
End With
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a, 1)
.Item(a(i, 1)) = Empty
Next
With Sheets("Feuil1")
a = .Range("a1").CurrentRegion.Value
End With
n = 1
For i = 2 To UBound(a, 1)
If .exists(a(i, 9)) Then
n = n + 1
For j = 1 To UBound(a, 2)
a(n, j) = a(i, j)
Next
End If
Next
'Restitution en Feuil3
If n > 1 Then
With Sheets("Feuil3").Range("a1")
.Resize(n, UBound(a, 2)) = a
With .CurrentRegion
.Font.Name = "calibri"
.Font.Size = "10"
.VerticalAlignment = xlCenter
.Borders(xlInsideVertical).Weight = xlThin
.BorderAround Weight:=xlThin
With .Rows(1)
.Interior.ColorIndex = 42
.BorderAround Weight:=xlThin
End With
.Columns.AutoFit
End With
.Parent.Activate
End With
Else
MsgBox "Aucune donnée"
End If
End With
End Sub