Private Sub Worksheet_Change(ByVal Target As Range)
Dim ref As Range
Set ref = Intersect(Target.Cells(1, 1), [A1:D1])
If Not ref Is Nothing Then
ref.Select
Application.ScreenUpdating = False
Me.AutoFilterMode = False
ref.Offset(, 26).EntireColumn.ClearContents 'RAZ colonne auxiliaire
If IsEmpty(ref) Then Exit Sub
With Range(Cells(2, ref.Column), Cells(Rows.Count, ref.Column).End(xlUp))
.AutoFilter 1, ref.Text & "*" 'filtre automatique
.SpecialCells(xlCellTypeVisible).Copy ref.Offset(, 26)
.AutoFilter 'désactive le filtre pour trier
ref.Offset(, 26).EntireColumn.Sort ref.Offset(, 26), Header:=xlYes 'tri
.AutoFilter 1, ref.Text & "*" 'replace le filtre
End With
If Not IsEmpty(ref.Offset(2, 26)) Then _
SendKeys "%{UP}" 'affiche la liste de validation
End If
End Sub