Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns(7).Resize(, Columns.Count - 6).ClearContents 'RAZ
[F:F].Validation.Delete 'RAZ
If ActiveCell.Column <> 6 Or ActiveCell.Row = 1 Then Exit Sub
If ActiveCell(1, 0) = "" Then Exit Sub
Dim x, h&, ligdeb&, d1 As Object, d2 As Object, i&
With [A1].CurrentRegion.Resize(, 7)
x = .Cells(ActiveCell.Row, 4)
h = Application.CountIf(.Columns(4), x)
If h = 0 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Columns(7).Insert 'colonne auxiliaire
.Cells(1, 7) = 1: .Columns(7).DataSeries 'numérotation
.Sort .Columns(4), Header:=xlYes 'tri sur la colonne D
ligdeb = Application.Match(x, .Columns(4), 0)
Set d1 = CreateObject("Scripting.Dictionary")
d1.Comparemode = vbTextCompare 'la casse est ignorée
Set d2 = CreateObject("Scripting.Dictionary")
d2.Comparemode = vbTextCompare 'la casse est ignorée
For i = ligdeb To ligdeb + h - 1
If UCase(.Cells(i, 5)) = "RUPTURE" Then d1(.Cells(i, 1).Value) = "" Else d2(.Cells(i, 1).Value) = ""
Next
.Sort .Columns(7), xlAscending, Header:=xlYes 'ordre initial
End With
Columns(7).Delete
With IIf(UCase(ActiveCell(1, 0)) = "RUPTURE", d2, d1)
If .Count Then
ActiveCell(1, 2).Resize(, .Count) = .keys
ActiveCell.Validation.Add xlValidateList, Formula1:="=" & ActiveCell(1, 2).Resize(, .Count).Address
End If
End With
Application.EnableEvents = True
End Sub