Need to apply data validation to a range

[jtemp57]

Hi, I want to apply the same data validation to a range of cells ("F8:F51") on worksheet"B". This validation is based on the value in cell "S3" also on worksheet"B" which is populated from a formula based on the value from a range "Unit" in worksheet"A". So for example if range "Unit" on worksheet "A" equals "Yes", then cell S3 mirrors it and also equals "Yes". I need range ("F8:F51") on worksheet "B" to validate a list based off of "Yes". I also want it to clear the validation in case the wrong data is input or there is no data in cell "S3". Thanks for your help. The code I started on is below... The code does not want to work at all....I think it has something to do with cell S3 on worksheet"B" being populated via a formula.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$S$3" Then
    If Target.Value = "Yes" Then
With Range("F8:F51").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=EO2_SubP"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
       
        ElseIf Target.Value = "No" Then
            With Range("F8:F51").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=EG2_SubP"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        
         ElseIf Target.Value = "Maybe" Then
            With Range("F8").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=EO3_SubP"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
End If
End If
End Sub

 

[rlv01]

The Select Case statement might suit your needs better

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    With Range("F8:F51").Validation
        If Target.Address = "$S$3" Then
            Select Case Target.Value
                Case "Yes"
                    .Delete
                    'Code to define validation goes here
                    
                Case "No"
                    .Delete
                    'Code to define validation goes here
                    
                Case "Maybe"
                    .Delete
                    'Code to define validation goes here
                    
                Case ""
                    .Delete
            End Select
        End If
    End With
End Sub

As for the validation rules, I have found that when I want to code VBA to set cell validations, using the macro recorder to record myself manually setting up the precise validation rules I want, then studying the recorder code for the exact syntax to use in my new macro s the way to go.

Also, if you are unable to manually set up your validation rule, it is unlikely it can be done via VBA.