add/insert data validation cell to if column value to left is a number

[mperrah]

Hello VBA legends,


Im trying to loop through the cells in column "E" if it has a number, add a data validation cell to the right.

I'm helping a co-worker automate a report.
He uses the workbook to track the status of an order. If an order has a shortage the report helps direct the needed actions.

The report has product orders output as an outline (2 levels)
Each order has a header (level 1), and each product has a header (level 2),
then the product items are listed from 1 to (less then 50), with a blank row above and below the order group.

I'm trying to add a validation list with four options to the right of each item: R/M, Pkg, Label, Other.

I have this code to insert the validation on selected cells.

Sub AddValidate()

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="R/M, Pkg, Label, Other, ' "
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

I'm trying to insert the DV in a loop through the cells of product items (all items are numeric values)
(I also am adding some header labels)

 Sub AddtoOrderTracking()
    
    Dim i As Integer
    Dim n As Integer
    Dim c As Integer           
                
        ActiveSheet.Outline.ShowLevels RowLevels:=2
        
        For i = 2 To 1000
            If Cells(i, 5).Value = "Order Quantity" Then
            
            Cells(i, 6).Value = "Shortages"
            
           ' this is where my code breaks<<<
           '     For n = 1 To 50
           '         If Cells(i + 1, 5).Value <> "" Then
           '             Cells(i + n, 6).Select
           '               Call AddValidate 'insert DV
           '               Else: End If
           '     Next n
                
            Cells(i, 7).Value = "Completed"
            Cells(i, 8).Value = "Comments"
                        
            End If
        Next i

End Sub

So to sum up, Im trying to loop through the cells in column "E" if it has a number, add the validation to the right

[mperrah]

screen capture too small?
Attachment 12979
ScreenCap2.jpg
Hope this helps.

Thank you in advance for any help you can offer.

-Mark

[mperrah]

Guess I needed to wrap my head around it a bit longer,
This works,
much love

 Sub AddValidateLoop()
    
    Dim i As Integer
    Dim n As Integer
    Dim c As Integer
                    
        ActiveSheet.Outline.ShowLevels RowLevels:=2
        
        For i = 2 To 1000
            If Cells(i, 5).Value <> "" And Cells(i, 6).Value = "" Then
                With Cells(i, 6).Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="R/M, Pkg, Label, Other, ' "
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
                        
            End If
        Next i

End Sub