So you want a color selection based on a product listing…

Private Sub Worksheet_Change(ByVal Target As Range)

This sheet has the product selection in column C (3)

'If Target.Address = "$B$2" Then
If Target.Column = 3 Then

Dim sProduct As String, sFilter As String
sProduct = Target.Value

We’re going to check the Color sheet for the product code – there’s not very many rows of products in this example – only rows 2 to 5 hold the product types.

Product Name look up reference column

For iRow = 2 To 6
If Sheets("Color").Range("U" & CStr(iRow)).Value = sProduct Then
sFilter = Sheets("Color").Range("V" & CStr(iRow)).Value
sFilter = "=OFFSET(Color!$" & sFilter & "$2,0,0,COUNTA(Color!$" & sFilter & ":$" & sFilter & ")-1,1)"
End If
Next iRow

Next we delete and recreate the data validation lists based on the filter we just created looking up the products.

Range("F" & Target.Row).Validation.Delete
Range("G" & Target.Row).Validation.Delete
If Len(sFilter) > 0 Then
If sProduct = "Add-On" Then
Range("F" & Target.Row).Validation.Add xlValidateList, , , sFilter
Range("F" & Target.Row).Validation.Add xlValidateList, , , "=Color!$D$2:$D$3"
Range("G" & Target.Row).Validation.Add xlValidateList, , , sFilter
End If
End If

End If

End Sub


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s