my objective is to do data validation for all cells in columns B through E in which there is a value in column A. i am able to get it to do the first row, but when it comes to changing the range to be used (B:E) i cannot seem to get it to adjust for the row that it is in. i think the fix is relatively simple i just cannot think of it. any ideas?
Dim i, n As Integer
n = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
n = n - 1
Range("B2").Select
Dim ran As Range
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
With ActiveCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$N$2:$N$3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Selection.AutoFill Destination:=Range("B:E"), Type:=xlFillDefault
ActiveCell.Offset(1, 0).Select
Loop
Bookmarks