Data Validation Range Question

S

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
 
T

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
 
S

Steph

Beautiful! Thanks Tom!!!

Tom Ogilvy said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top