Data Validation With rules

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$2" Then
            Select Case Target.Value
                Case "All"
                                   
                       With ThisWorkbook.Sheets(1).Range("D2").Validation
                            .Delete
                            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=allrng"
                       End With
                Case "X"
                        With ThisWorkbook.Sheets(1).Range("D2").Validation
                            .Delete
                            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=xrng"
                       End With
                Case "Y"
                        With ThisWorkbook.Sheets(1).Range("D2").Validation
                            .Delete
                            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=yrng"
                       End With
            End Select
        End If
End Sub


Download file:

Data Validation with rules