Private Sub Worksheet_Change(ByVal Target As Range)
Dim plage As Range, t, ub&, i%, col%, x$, j&
Set plage = Feuil1.[B2:B10] 'CodeName et adresse à adapter
UsedRange.Name = "Tableau1" '1er nom défini
UsedRange.Offset(, 1).Name = "Tableau2" '2ème nom défini
t = [Tableau1] 'matrice, plus rapide]
ub = UBound(t)
For i = 1 To plage.Count
col = 2 * i - 1: x = ""
For j = 1 To ub
If t(j, col) <> "" Then x = x & "," & t(j, col)
Next j
With plage(i).Validation
.Delete
.Add xlValidateList, Formula1:=Mid(x, 2) 'liste en dur
End With
Next i
End Sub