I have code that prevents the user from choosing a duplicate choice from a list - everything works pretty much the way it should, but if the user Deletes or clears content in that range I get a run-time error '13' Type Mismatch error.
What can be added to not have this happen? When I debug it point to
Set RngBeg = Rng.Find("", Target, xlValues, xlWhole, xlByRows, xlPrevious, False, False, True)
The complete Code is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Match As Range
Dim Rng As Range
Dim RngBeg As Range
Dim RngEnd As Range
Dim x As String
If Not Intersect(Target, Range("B:B")) Is Nothing Then
With Application.FindFormat
.Clear
.Interior.Color = RGB(242, 220, 219)
End With
Set Rng = Range("B9:B67").Resize(ColumnSize:=5)
Set RngBeg = Rng.Find("", Target, xlValues, xlWhole, xlByRows, xlPrevious, False, False, True)
If Not RngBeg Is Nothing Then
Set RngEnd = Rng.Find("", Target, xlValues, xlWhole, xlByRows, xlNext, False, False, True)
Set Rng = Range(RngBeg.Offset(1, 0), RngEnd.Offset(-1, 0)).Resize(ColumnSize:=5)
Set Match = Rng.Find(Target.Value, , xlValues, xlWhole, xlByRows, xlNext, False, False, False)
If Not Match Is Nothing Then
x = Match.Address
Set Match = Rng.FindNext(Match)
If Not Match Is Nothing Then
If Match.Address = x Then Exit Sub
Application.EnableEvents = False
Target.Value = Empty
Target.Select
MsgBox "Please make another selection. Duplicates are not allowed."
Application.EnableEvents = True
End If
End If
End If
End If
Application.FindFormat.Clear
End Sub
Bookmarks