Auto change of validated cell when source in data validation changed

[AnnieAppleBee]

Hello,

I have a sheet here that I use data validation in the master list worksheet (Example, Column F chooses the fabric type, Column G selects the fabric color, which is dependent of F), the source list is in material color worksheet. How do I allow automatic cell update when the source in the list change? I tried this code in the master list worksheet in visual basics (applied the code to master list worksheet) but didn't work.

#
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strValidationList As String
Dim strVal As String
Dim lngNum As Long

On Error GoTo Nevermind
strValidationList = Mid(Target.Validation.Formula1, 2)
strVal = Target.Value
lngNum = Application.WorksheetFunction.Match(strVal, Range(strValidationList), 0)

If strVal <> "" And lngNum > 0 Then
Application.EnableEvents = False
Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"
End If

Nevermind:
Application.EnableEvents = True

End Sub
#

Your help is appreciated!



Thanks,
AnnieAppleBee

[dflak]

The bad news is that you will have to normalize your color data to make the method I laid out work. See the attached word document for more information.

The good news is that it does not require VBA.