Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'fonctionne correctement si [Reference] a été triée
Dim plage As Range, r As Range, cel As Range
Dim ref As Range, t$, lig As Variant, h&, F$
Set plage = Range("C5") 'à adapter
Set r = Intersect(Target, plage)
If Not r Is Nothing Then
For Each cel In r
With cel.Validation
.Delete
If Not IsEmpty(cel) Then
Set ref = [Packages] 'à adapter éventuellement
t = cel & "*" 'caractère générique *
lig = Application.Match(t, ref, 0)
If IsNumeric(lig) Then
h = Application.CountIf(ref, t)
If h > 1 Then
F = "=" & ref(lig).Resize(h).Address
.Add xlValidateList, xlValidAlertStop, xlBetween, F
.ShowError = False
End If
End If
End If
End With
Next
r.Select 'facultatif
End If
'Macro that hide blanks lines for the Master Spec section
Application.ScreenUpdating = False
If Target.Address <> "$C$5" And Len(Target.Value) > 1 Then Exit Sub
For lig = 15 To 18 'modification de l par lig
Rows(lig).Hidden = Cells(lig, "C") = "" 'modification de l par lig
Next
Range("C5").Select
End Sub