Data Validation Range Question

[Steph]

Hello. The code below is mostly from the recorder which inserts data
validation for a continuous range. My question is this - Can I make the
range to insert the data validation into be the range (in column B) that has
a 1 in column Z? For example, if cell Z1 has a 1 in it, then create the
data validation in cell B1, and so on all the way down to the bottom of the
list in column B. Thanks in advance!!

wks.Range("B5:B" & wks.Range("D65536").End(xlUp).Row).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="Y,N"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[Tom Ogilvy]

wks.Range("B5:B" & wks.Range("D65536").End(xlUp).Row).Select
for each cell in selection
if cells(cell.row,"Z").Value = 1 then
With cell.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop,Operator:= _
xlBetween, Formula1:="Y,N"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End if
Next

 

[Steph]

Beautiful! Thanks Tom!!!

wks.Range("B5:B" & wks.Range("D65536").End(xlUp).Row).Select
for each cell in selection
if cells(cell.row,"Z").Value = 1 then
With cell.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop,Operator:= _
xlBetween, Formula1:="Y,N"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End if
Next