list validation in excel; what if an item in list changes??

Hello,

I have a used a list validation in my excel sheet,
which works fine when i want to insert a value from
a specified list in a cell.

(the specific list is specified in A1:A3):
A1:YES
A2:NO
A3:NO COMMENT

but now, if i change for example in the list A3
from "NO COMMENT" to "UNSPECIFIED" i have the following
problem:
- all cells linked to the list (that now changed) where
previous "NO COMMENT" was correctly validated should
not be valid anymore since an item in my list changed.

Excel does not do this automatically. In a new cell i can
choose between the three items correctly, but what about the
old cells! they are corrupt (against my new list and excel doesn't see this!)

can i enable this autocheck on change or is there a function in vba or something that forces the validation?

what now???

to start off, you will need to copy the current values to another location on 'worksheet_activate' to reference if the list range changes.

then set the range (e.g. give it a name), and you should be able to reference it in code.

VB Code:

Range("MyBook.xls!ListRange")

when it changes you check the values to the values you copied on activate, and if different, do a find/replace of the old value for the new.

heres come quick code:

VB Code:

Sub CheckValues()
 
    Dim intcount As Integer
    
    intcount = 1
    
    For Each c In Range("ListRange")
        If c.Text <> Range("B" & intcount).Text Then
                Cells.Replace What:=Range("B" & intcount).Text, Replacement:=c.Text, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
intcount = intcount + 1
        End If
    Next c
 
End Sub
 
Private Sub Worksheet_Activate()
 
'copy values to next column
    Range("A1:A3").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
CheckValues
 
End Sub

just to add..done some testing and you will need to make sure that the named range and column B range is the same before adding this code or it goes a bit wonky..:(