Adding more drop down list with my macro?

[b_rianv]

Good evening everyone,

I am having trouble adding more drop down list too my macro. I already have column (B) done, still need to do columns (C,D,E,F,G). I Also have my macro adding a new row at (A2) when I click my Active X button found in cells (K1,L1). So when I click my Active X button a New Row is added as well as a drop down list in cell (B2),I know you can't see my Active X button well but I can't get the text too show because it is a small button and want it that way,have tried everything in the properties area.

Can someone help me add these other drop down list commands to my macro for columns (C2,D2,E2,F2,G2)?

Here is my workbook

[b_rianv]

Here is my code...

Private Sub CommandButton1_Click()

Sheets("Search").Range("A2").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("Search").Range("A2:G2").Select
Selection.Borders.Weight = xlThin

Sheets("Search").Range("B2").Select
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=data_2!$A$2:$A$19"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub

[b_rianv]

Thank you for your for your time

[Paul_Hossler]

Not exactly sure make it is you're trying to do, so this is my best guess

Option Explicit

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim r As Range
    With Selection.EntireRow
        For i = 1 To 6
            
            With .Cells(1, i + 1).Validation
                Set r = Worksheets("data_2").Cells(2, i)
                Set r = Worksheets("data_2").Range(r, r.End(xlDown))
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=data_2!" & r.Address
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
        Next I
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A:G").Interior.Color = xlColorIndexNone
    Intersect(Target.EntireRow, Range("A:G")).Rows(1).Interior.Color = RGB(255, 255, 0)
End Sub

[b_rianv]

Thank you Paul, but it isn't working plus my search stopped work as well. Please look at my code, my code allows me to
1.) Add a NEW ROW, from (A2,G2)
2.) Insert a Drop down list in to the cell (B2) which gets its data from (data_2) workbook, which is column ( A2)
I want to keep everything the way it is.
What I want to do is have my code ADD more drop down lists to the other columns (C2,D2,E2,F2,G2) with the data from data_2 worksheet, when I click the insert button as well

[Paul_Hossler]

Sorry, I removed code that wasn't pertinent to the question, and made the incorrect assumption that you wanted to insert anywhere

Try this version

Option Explicit

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim r As Range
    Range("A2:G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        
    With Range("A2:G2")
        .Borders.Weight = xlThin
    
        For i = 1 To 6
            With .Cells(1, i + 1).Validation
                Set r = Worksheets("data_2").Cells(2, i)
                Set r = Worksheets("data_2").Range(r, r.End(xlDown))
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=data_2!" & r.Address
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
        Next I
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A:G").Interior.Color = xlColorIndexNone
    Intersect(Target.EntireRow, Range("A:G")).Rows(1).Interior.Color = RGB(255, 255, 0)
End Sub

[b_rianv]

That my friend is GREAT, Thank you so much!!