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 to automatically update the cell when the list change? I tried using macro, pasted this code in the master list worksheet but didn't work.

>>> use code - tags <<<

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

 

[Marc L]

Hello, according to any Excel forum rules - a must read before creating any thread ! - two issues with your thread :​

1) use the code icon in the 3 dots menu when posting any code;​

2) you forgot the link for each same thread created on another Excel forum(s) in order to avoid some wild cross posting …​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org