Find and Replace Within Validation Limitations

[DMumme]

I have a worksheet with cells that have validation limitations allowing a list as a source. These cells need to be editable by all users. When changing a validation cell manually, the input is limited to the validation rule assigned. Due to the vast number of cells that may need to be changed at one time, the user may choose to use Find and Replace in Excel to perform the changes. When changing a validation cell(s) using Find and Replace, the validation limitation is ignored and the change(s) are made in violation of the validation limitations.

Is there a way to allow Find and Replace to be used while restricting the replacement input to match the validation requirements for the cell(s)? A VBA alternative (customized Find and Replace) would be an acceptable answer.

 

[kennypete]

Something like this...

Sheet with cells where validation is a fixed A,C,G,T as illustrated:

Code, noting this is rudimentary, but conceptually does what you want:

Sub subReplaceAndCheckValidation()
    f$ = InputBox("Find text")
    r$ = InputBox("Replace text")
    For Each cell In ActiveSheet.UsedRange.Cells
        If cell.Value = f$ Then
            If InStr(cell.Validation.Formula1, r$) > 0 Then
                cell.Value = r$
            Else
                x = MsgBox("Change not allowed for cell " & Split(Cells(1, cell.Column).Address(True, False), "$")(0) & cell.Row, , "Validation issue")
            End If
        End If
    Next cell
End Sub

It will pop up a message box when the replacement text is not in the validation list and not make any changes/skip and move on. This could be extended, of course, to list the cells rather than a message box each time, or abort, etc.

So if find is A and replace is C, all good, but find A and replace U ... no.