Sub ClientsCommuns()
Dim plage1 As Range, plage2 As Range, n%, r As Range, lig&, mlig&
Set plage1 = Workbooks("Fichier1.xls").Sheets("Feuil1").UsedRange 'noms à adapter
Set plage2 = Workbooks("Fichier2.xls").Sheets("Feuil1").UsedRange 'noms à adapter
n = Val(InputBox("Entrez le NUMERO de la colonne de comparaison :"))
If n < 1 Or n > plage1.Columns.Count Then Exit Sub
Application.ScreenUpdating = False
Cells.Clear 'RAZ
For Each r In plage1.Rows
If Application.CountIf(plage2.Columns(1), r.Cells(n)) Then
lig = lig + 1
r.Copy Cells(lig, 1)
End If
Next
mlig = lig + 1 'mémorise
For Each r In plage2.Rows
If Application.CountIf(plage1.Columns(1), r.Cells(n)) Then
lig = lig + 1
r.Copy Cells(lig, 1)
End If
Next
Rows(mlig).Delete 'facultatif, supprime la 2ème ligne de titres
End Sub