Sub Liste_Plats()
Dim f4 As Worksheet, f5 As Worksheet
Dim DerLig_Plats As Long
Dim i As Long, j As Long
Dim Plat As String
Application.ScreenUpdating = False
Set f4 = Sheets("Plats")
Set f5 = Sheets("Repas")
DerLig_Plats = f4.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To 9 'on passe tous les plats en revue
Cpt = 0
Plats = ""
Type_Plat = f5.Cells(4, i)
For j = 2 To DerLig_Plats
If f4.Cells(j, "B") = Type_Plat Then
Plats = Plats & "," & f4.Cells(j, "A")
Cpt = Cpt + 1
End If
Next j
If Cpt <> 0 Then
Plats = Right(Plats, Len(Plats) - 1)
With f5.Cells(5, i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Application.Transpose(Plats)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = Type_Plat
.ShowInput = True
.ShowError = True
End With
End If
Next i
Set f4 = Nothing
Set f5 = Nothing
End Sub