Background: I want to validate a range of cells A1-A10 on sheet1. The valid values come from Sheet2, cells C1-C8 which contains the values 1-8 (C1=1, C2=2, etc.) The code runs without errors but the dropdown values in A1-A10 are blank and not the allowable choices 1-8. What is wrong with the following code?
[code]
Sub test()
Dim rng As Range
Dim range1 As Range
Set rng = Sheets("sheet1").Range("A1:A10")
Set range1 = Sheets("sheet2").Range("C1:C8")
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=" & range1.Address
End With
end Sub
{/code]
Thanks for any suggestions.
Bookmarks