Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NP2 As Range, Cod2$, Cod3$, Mnt&, NP&, dlig&, lig&
With Target
If .CountLarge > 1 Then Exit Sub
If .Row < 6 Then Exit Sub
If .Column <> 4 Then Exit Sub
Mnt = .Value
If Mnt = 0 Then
.Offset(, 1).Resize(, 2).ClearContents
Else
NP = .Offset(, -3): If NP = 0 Then Exit Sub 's'il n'y a pas de N° Pièce
With Worksheets("source")
dlig = .Cells(Rows.Count, 1).End(3).Row: Application.ScreenUpdating = 0
For lig = 6 To dlig
Set NP2 = .Cells(lig, 1)
If NP2 = NP And NP2.Offset(, 5) = Mnt Then
Cod2 = NP2.Offset(, 3): Cod3 = NP2.Offset(, 4): Exit For
End If
Next lig
End With
If Cod2 <> "" Then .Offset(, 1) = Cod2: .Offset(, 2) = Cod3
End If
End With
End Sub