Private Sub Worksheet_SelectionChange(ByVal c As Range)
Dim F2 As Worksheet, F3 As Worksheet, MaListe As Range, col%
Dim Encours As Range, dat&, i As Variant, Dispo As Range
Dim equipe$, Param As Range, n&
Set F2 = Feuil2 'CodeName de la feuille"Dispo"
Set F3 = Feuil3 'CodeName de la feuille "Parametre"
Set MaListe = F3.[F2] 'cellule à adapter eventuellement
Set c = ActiveCell
col = c.Column
[B:D].Validation.Delete 'RAZ
MaListe.Resize(F3.Rows.Count - MaListe.Row + 1).ClearContents 'RAZ
If col < 2 Or col > 4 Or IsDate(c(1, 2 - col)) Or c(1, 2 - col) = "" Then Exit Sub
'---zone Encours---
Set Encours = c(1, 2 - col).CurrentRegion.Columns(col).Cells
dat = Encours(1, 2 - col).Value2
'---zone Dispo---
i = Application.Match(dat, F2.Rows(1), 0)
If IsError(i) Then Exit Sub
Set Dispo = F2.Cells(1, i).CurrentRegion.Columns(col - 1).Offset(2).Cells
'---zone Param---
equipe = c(1, 2 - col)
i = InStr(equipe, "/")
If i Then equipe = Left(equipe, i - 1)
i = Application.Match(equipe, F3.Rows(1), 0)
If IsError(i) Then Exit Sub
Set Param = F3.[A1].CurrentRegion.Columns(i).Offset(1)
'---MaListe---
For i = 1 To Dispo.Count - 2
If Dispo(i) <> "" Then _
If Application.CountIf(Param, Dispo(i)) Then _
If Application.CountIf(Encours, Dispo(i)) = 0 Or Dispo(i) = c _
Then n = n + 1: MaListe(n) = Dispo(i)
Next
If n Then
With MaListe.Resize(n)
.Sort MaListe, xlAscending, Header:=xlNo 'tri (facultatif)
.Name = "MaListe"
End With
c.Validation.Add xlValidateList, Formula1:="=MaListe" 'liste de validation
Else
With c.Validation
.Add xlValidateTextLength, Formula1:=0, Formula2:=0
.ErrorMessage = "Personne n'est disponible..."
End With
End If
End Sub