What changes to columns 2,3 & 4 do you want to make? Please describe in detail.
Hi mumps
Thanks for getting back to me.
In column 1 a drop down list allows selection of an order item i.e. bottle, case, keg, pack
If I select bottle for example then I want my code to change column 2, 3 & 4 to either text n/a if that column doesn't apply or if it does, to change the data validation address to a specific table with further selection data applicable to bottle. I have posted the code that I have written but it only works on the specified row currently where I would like it to apply to any row in the table without having to repeat this code for every row of the table.
I hope this helps & thanks so much for your help!
Regards
G
Sub aaSelect()
' BOTTLE
Application.ScreenUpdating = False
If Range("F5") = "Bottle" Then
Range("G5:J5").Select
Selection.ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
ActiveCell.FormulaR1C1 = "n/a"
Application.ScreenUpdating = False
Range("H5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Unit_Size1[Unit Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("I5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Measure1[Measure]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("J5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Serve_Size1[Serve Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("F5").Select
End If
' CASE
If Range("F5") = "Case" Then
Application.ScreenUpdating = False
Range("G5:J5").Select
Selection.ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Case_Size3[Case Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("H5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Unit_Size3[Unit Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("I5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Measure3[Measure]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("J5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Serve_Size3[Serve Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Range("F5").Select
Application.ScreenUpdating = False
' EACH
Application.ScreenUpdating = False
If Range("F5") = "Each" Then
Range("G5:J5").Select
Selection.ClearContents
Application.ScreenUpdating = False
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
ActiveCell.FormulaR1C1 = "n/a"
Application.ScreenUpdating = False
Range("H5").Select
ActiveCell.FormulaR1C1 = "1"
Application.ScreenUpdating = False
Range("I5").Select
ActiveCell.FormulaR1C1 = "Each"
Application.ScreenUpdating = False
Range("J5").Select
ActiveCell.FormulaR1C1 = "Each"
Range("F5").Select
End If
' KEG
If Range("F5") = "Keg" Then
Application.ScreenUpdating = False
Range("G5:J5").Select
Selection.ClearContents
Application.ScreenUpdating = False
Range("G5:J5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
ActiveCell.FormulaR1C1 = "n/a"
Application.ScreenUpdating = False
Range("H5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Unit_Size4[Unit Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("I5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Measure4[Measure]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("J5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Serve_Size4[Serve Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Range("F5").Select
' PACK
If Range("F5") = "Pack" Then
Application.ScreenUpdating = False
Range("G5:J5").Select
Selection.ClearContents
Application.ScreenUpdating = False
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
ActiveCell.FormulaR1C1 = "n/a"
Application.ScreenUpdating = False
Range("H5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Unit_Size6[Pack Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("I5").Select
ActiveCell.FormulaR1C1 = ""
Application.ScreenUpdating = False
Range("I5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Serve_Size6[Serve Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("J5").Select
ActiveCell.FormulaR1C1 = "Each"
Range("F5").Select
End If
Application.ScreenUpdating = True
End Sub