VBA Code for a loop with a twist

[slabs]

Hi,

On Book1.xlsm there is a product table for company BF with all their products, ranges for each product and colour for each range.

For spreadsheet "Quotations" you can choose the company, product, range and colour on a dropdown list, as per row 2. The VBA code I wrote only works for row 2, how do I get it so that for each row 2 - 31 a person can input different choices?

Am not sure how to loop it in this case, because for each row the parameters in row one of the dropdown spreadsheet need to change?

Thanks

[dflak]

I don't see anything attached. Based on your description, I recommend assigning the range a name. Make it a named dynamic range. When I see how the data is organized, I might be able to give you a better answer.

[slabs]

Hi,

Hopefully the sheet is attached now. Thanks for looking into it.

Regards

André

[dflak]

I gave this a quick test:

Private Sub Worksheet_Change(ByVal Target As Range)

' $c$2 is the Company Dropdown
' $d$2 is the Product Dropdown
' $e$2 is the Range Dropdown

If Intersect(Target, Range("$C$2:$E$30")) Is Nothing Then Exit Sub

Select Case Left(Target.Address, 2)
    Case "$C"                                                 ' The region dropdown is changed
         Worksheets("Dropdown").Range("$D$1") = Target.Value    ' Set the filter on the company pivot table
    Case "$D"                                                 ' The product dropdown is changed
         Worksheets("Dropdown").Range("$G$1") = Target.Value    ' Set the filter on the product pivot table
    Case "$E"                                                 ' The range dropdown is changed
         Worksheets("Dropdown").Range("$J$1") = Target.Value    ' Set the filter on the range pivot table
    Case Else
        MsgBox "An error has occured", vbOKOnly, "Invalid Selection"
End Select

End Sub

[slabs]

Excellent! Much appreciated

[dflak]

I don't see any code.

OK. I see it on the page.