How to associated a drop down macro to a new column

[wzthxj]

I have a workbook in which a new column has been added via the following:

Sub AddColumnsWithHeadersResize()

With Sheet4.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 4)
.Value = Array("Observations", "Deviations", "Potential Debit Amount", "Debit Amount")
.Resize(, 1).ColumnWidth = 30
.Offset(, 1).Resize(, 1).ColumnWidth = 20
.Offset(, 2).Resize(, 2).ColumnWidth = 10
.Offset(, 2).Resize(, 2).ColumnWidth = 10
End With
End Sub

The new column "Deviations" will contain a drop down menu in each cell. I need to somehow associate the drop-down with this column, with the caveat that the number of columns before the 4 new ones just created varies by user. I created a macro but it's currently associated with a specific cell (which will change). Thanks for your help.

Range ("**").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Data!$A$2:$A$11"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[Fluff]

How many cells do you want to put the validation into?

 

[wzthxj]

4000. When I made the macro, I just clicked and dragged to copy it from the top cell downward.

 

[Fluff]

Ok, how about

Sub wzthxj()
   
   With Sheet4.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 4)
      .Value = Array("Observations", "Deviations", "Potential Debit Amount", "Debit Amount")
      .ColumnWidth = Array(30, 20, 10, 10)
      With .Offset(1, 1).Resize(4000, 1).Validation
         .Delete
         .Add xlValidateList, , , "=Data!$A$2:$A$11"
      End With
   End With
End Sub

I've added it to the previous code, so you can delete that.

 

[wzthxj]

You are awesome! I was about to give up. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.