recherche dans 2 fichiers les lignes ayant un champs en commun

Sub ClientsCommuns()
Dim plage1 As Range, plage2 As Range, 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
Application.ScreenUpdating = False
Cells.Clear 'RAZ
For Each r In plage1.Rows
  If Application.CountIf(plage2.Columns(1), r.Cells(1)) 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(1)) 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

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

Sub ClientsCommuns(n As Variant)
Dim plage1 As Range, plage2 As Range, lig&, r As Range, mlig&
On Error Resume Next
Set plage1 = Workbooks("EX2010.xls").Sheets("Exportation").UsedRange 'noms à adapter
Set plage2 = Workbooks("EX2011.xls").Sheets("Exportation").UsedRange 'noms à adapter
If Err Then MsgBox "'EX2010.xls' et 'EX2011.xls' doivent être ouverts...": Exit Sub
lig = 1
Application.ScreenUpdating = False
[2:65536].Clear 'RAZ
For Each r In plage1.Rows
  If Application.CountIf(plage2.Columns(1), r.Cells(1)) And _
    r.Cells(17) = n Or r.Cells(17) = "NB" 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(plage2.Columns(1), r.Cells(1)) And _
    r.Cells(17) = n Or r.Cells(17) = "NB" Then
    lig = lig + 1
    r.Copy Cells(lig, 1)
  End If
Next
If Cells(mlig, 17) = "NB" Then Rows(mlig).Delete 'facultatif, supprime la 2ème ligne de titres
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$1" Then Exit Sub
ClientsCommuns [D1]
End Sub