I answered a question over at superuser.com the other day having to do with syncing data validation. If you use data validation that links to a range, and you change the range, the data may no longer be valid. I have a range in A1:A3 that I’m using as a data validation list. In D7, I’ve selected the first item.
Now if I go change A1, the D7 value is no longer valid. Unless, I have some VBA to keep it in sync.
Here’s the code:
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
I think the next step is to make it more generic so it will work with any named range. I’ve never personally needed it, though.