T4233_Data Validation List In Worksheet Change

https://excel-egy.com/forum/t4233
---------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x, lr As Long

    If Target.Cells.CountLarge > 1 Then Exit Sub

    If Target.Address = "$B$1" Then
        x = Application.Match("احياء " & Target.Value, Rows(3), 0)

        If Not IsError(x) Then
            With Range("D1").Validation
                .Delete
                lr = Application.Max(4, Cells(Rows.Count, x).End(xlUp).Row)
                If Cells(4, x).Value = "" Then Exit Sub

                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & Range(Cells(4, x), Cells(lr, x)).Address
            End With
        Else
            Range("D1").Validation.Delete
        End If
    End If
End Sub