I'm trying to apply validation using a dynamic list from sheet4 to a range on sheet10, but the following code fails at the ".Add" line? I suspect it might be looking for a string not a range. Anyone able to explain why, and how to fix it?
Code
Sub DataValidation()
Dim rList As Range
Set rList = Sheet4.Range("C2", Sheet4.Range("C" & Rows.Count).End(xlUp))
With Sheet10.Range("x7:x12")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
End Sub
Display More