Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim FParam As Worksheet
Dim Fam As Range
Dim V As String
Dim L As Long
Dim F As Byte, C As Byte
On Error GoTo Fin
V = Target.Validation.Formula1
If Left(V, 4) = '=mDF' Then
Set FParam = Sheets('mDF')
Set Fam = FParam.Range('mDF')
If V = '=mDF' Then
For F = 1 To Fam.Count
If Fam(F) = Target.Text Then
C = F
Exit For
End If
Next F
If C > 0 Then
L = FParam.Cells(65536, C).End(xlUp).Row
ActiveWorkbook.Names.Add Name:='mDF1', RefersTo:='=mDF!' & _
Range(Cells(2, C), Cells(L, C)).Address
End If
End If
Target.Validation.Modify Formula1:=IIf(V = '=mDF1', '=mDF', '=mDF1')
If V = '=mDF' Then ActiveCell.EntireRow.Range('A1') = ActiveCell
End If
End If
Fin:
End Sub