Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a, f As Worksheet, P As Range, arrivee, depart, liste()
Dim lieu, flag As Boolean, c As Range, n&
a = Array("S1", "S2", "S3") 'liste des lieux à adapter
Set f = Sheets("Liste")
Set P = Intersect(Range("G2:G" & Rows.Count), Me.UsedRange.EntireRow)
f.Range("A2:A" & f.Rows.Count).ClearContents 'RAZ
P.EntireColumn.Validation.Delete 'RAZ
If Intersect(ActiveCell, P) Is Nothing Then Exit Sub
arrivee = ActiveCell(1, -1).Value2
depart = ActiveCell(1, 0).Value2
If depart > arrivee And IsNumeric(arrivee) And IsNumeric(depart) Then
ReDim liste(1 To P.Rows.Count, 1 To 1)
For Each lieu In a
flag = True
If ActiveCell <> lieu Then
For Each c In P
If c = lieu And Not (c(1, 0) < arrivee Or c(1, -1) > depart) _
Then flag = False: Exit For
Next c
End If
If flag Then n = n + 1: liste(n, 1) = lieu
Next lieu
End If
With ActiveCell.Validation
If n Then 'liste de validation
f.[A2].Resize(n) = liste
f.[A2].Resize(n).Name = "Liste"
.Add xlValidateList, Formula1:="=Liste"
Else 'interdiction d'entrer une valeur
.Add xlValidateTextLength, Formula1:="0", Formula2:="0"
.ErrorMessage = "Dates à revoir !"
End If
End With
End Sub