Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, Q As Range
Set P = [B5:D5]: Set Q = Feuil2.[I8:I11] 'Feuil2 : CodeName de la feuille Source
Macro Target, P, Q
Set P = [B4:D4]: Set Q = Feuil2.[A62:A66]
Macro Target, P, Q
End Sub
Sub Macro(Target, P As Range, Q As Range)
Dim c As Range, n%
If Intersect(Target, P(1)) Is Nothing Then Exit Sub
P(3).Validation.Delete
Set c = Q.Find(P(1), , xlValues)
If c Is Nothing Then P(3) = "": Exit Sub
n = Application.CountA(c(1, 2).Resize(, 4))
If n = 1 Then
P(3) = c(1, 2)
Else
P(3) = ""
P(3).Validation.Add xlValidateList, Formula1:="=" & c(1, 2).Resize(, n).Address(External:=True)
P(3).Select
End If
End Sub