This seems dangerous, but I can't see any problem with it. Basically if you change anything in Foobar, it searches every cell on the sheet that has Data Validation. If the DV points to Foobar and the value isn't in the list, then it must have been the value that was changed. It worked with my limited testing. Let me know if you see any flaws.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rFound As Range
'Only run this when a cell in Foobar is changed
If Not Intersect(Target, Me.Range("Foobar")) Is Nothing Then
'Go through every data validation cell in the sheet
For Each rCell In Me.Cells.SpecialCells(xlCellTypeAllValidation).Cells
'if the DV in the cell points to foobar
If rCell.Validation.Formula1 = "=Foobar" Then
'See if the cell's value is in the Foobar list
Set rFound = Me.Range("Foobar").Find(rCell.Value, , xlValues, xlWhole)
'If it's not in the list, it must be the one that
'changed, so changed it
If rFound Is Nothing Then
Application.EnableEvents = False
rCell.Value = Target.Value
Application.EnableEvents = True
End If
End If
Next rCell
End If
End Sub
Note that this goes in the Worksheet's module, not a standard module. As always, test code on a copy of your workbook.
Not quite clear to me what you are trying - maybe a screenshot would be helpful? – mindless.panda – 2010-03-26T13:38:26.680
1I've added a screen shot and a workflow – Vinzz – 2010-03-26T13:53:02.617