Sélection d'éléments d'une liste à un tableau

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim L As Long, T(), Solu As String, C As Long
If Target.Count <> 1 Or Target.Column <> 5 Then Exit Sub
L = WorksheetFunction.Match(Me.Cells(Target.Row, "B").Value, Feuil1.Columns("B"), 0)
ReDim T(1 To 2, 1 To 1)
For C = 1 To 5
   Solu = Feuil1.Cells(L, C * 2 + 4).Value
   If Solu = "" Then Exit For
   ReDim Preserve T(1 To 2, 1 To C)
   T(1, C) = Solu: T(2, C) = Feuil1.Cells(L, C * 2 + 5).Value
   Next C
Me.[I2:M3].ClearContents
Me.[I2].Resize(2, UBound(T, 2)).Value = T
With Target.Validation
   .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
      Formula1:="=" & Me.[I2].Resize(, UBound(T, 2)).Address
   .IgnoreBlank = True
   .InCellDropdown = True
   .ShowInput = True
   .ShowError = True
   End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Or Target.Column <> 5 Then Exit Sub
With Target.Offset(, 2)
   .FormulaR1C1 = "=INDEX(R3C9:R3C13,MATCH(RC5,R2C9:R2C13,0))"
   .Value = .Value: End With
End Sub