How to add data validation to a cell using VBA

Solution 1:

Use this one:

Dim ws As Worksheet
Dim range1 As Range, rng As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")

Set range1 = ws.Range("A1:A5")
Set rng = ws.Range("B1")

With rng.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & ws.Name & "'!" & range1.Address
End With

Note that when you're using Dim range1, rng As range, only rng has type of Range, but range1 is Variant. That's why I'm using Dim range1 As Range, rng As Range.
About meaning of parameters you can read is MSDN, but in short:

• Type:=xlValidateList means validation type, in that case you should select value from list
• AlertStyle:=xlValidAlertStop specifies the icon used in message boxes displayed during validation. If user enters any value out of list, he/she would get error message.
• in your original code, Operator:= xlBetween is odd. It can be used only if two formulas are provided for validation.
• Formula1:="='" & ws.Name & "'!" & range1.Address for list data validation provides address of list with values (in format =Sheet!A1:A5)