Auto change of validated cell when source in data validation changed

[annieapplebee]

Hello,

I have a sheet here that I use data validation on all columns in the master list tab, the list is generated in material color tab. How do I make it auto update when the list change?

Your help is appreciated!

Thanks,
AnnieAppleBee

 

[annieapplebee]

I have tried this code but didn't work.
>>> use code - tags <<<

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)'Author:    Jerry Beaucaire,  8/12/2011'Summary:   Make choices from DV drop downs into formulas, so any changes'           in the source lists will flow out to the already filled in cells'           6/8/2016 correction to allow named range to exist anywhere in the workbookDim strValidationList As StringDim strVal As StringDim lngNum As Long
On Error GoTo NevermindstrValidationList = Mid(Target.Validation.Formula1, 2)strVal = Target.ValuelngNum = Application.WorksheetFunction.Match(strVal, ThisWorkbook.Names(strValidationList).RefersToRange, 0)
If strVal <> "" And lngNum > 0 Then    Application.EnableEvents = False    Target.Formula = "=INDEX(" & strValidationList & ", " & lngNum & ")"End If
Nevermind:    Application.EnableEvents = True   End Sub

Jerry Beaucaire's - Excel Assistant - Update Prior Choices (google.com)