Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = Range("d1").Column Then Target.Offset(, 1) = ""
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim n&, t, d As New Dictionary, i&
If Target.Count = 1 And Target.Column = Range("d1").Column Then
n = Application.WorksheetFunction.CountA(Columns("a:a"))
t = Columns("a:a").Resize(n): d.CompareMode = TextCompare
For i = 2 To UBound(t)
If t(i, 1) <> "" Then d(t(i, 1)) = ""
Next i
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(d.Keys, ",")
End With
ElseIf Target.Count = 1 And Target.Column = Range("e1").Column Then
n = Application.WorksheetFunction.CountA(Columns("a:a"))
t = Columns("a:b").Resize(n): d.CompareMode = TextCompare
For i = 2 To UBound(t)
If t(i, 1) = Target.Offset(, -1) And Target.Offset(, -1) <> "" Then d(t(i, 2)) = ""
Next i
With Target.Validation
.Delete
If d.Count > 0 Then
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(d.Keys, ",")
End If
End With
End If
End Sub