Excel Workbook - Prevent Value Duplicates in one column all worksheets of a workbook

I have a excel workbook with many rows of data.  There are also many worksheets - each worksheet relates to a week of data.  In column E of all worksheets combined there cannot be duplicates.  So I want the workbook to prevent me from entering a duplicate value.  A prompt saying "Duplicate Found" or the cell going red would be good.

I'd imagine this would be done on a change event with VB of a Macro Enabled Workbook
iitsAsked:
Who is Participating?
 
Ryan ChongCommented:
just a quick test...

in each of the worksheet, try add:
Private Sub Worksheet_Change(ByVal Target As Range)
    Call checkDuplicate(Target)
End Sub

Open in new window

then create a public module and add:
Public Sub checkDuplicate(v As Range)
    isDup = False
    
    If v.Cells.Count > 1 Then Exit Sub
    
    If v.Value = "" Then
        isDup = False
        Exit Sub
    End If
    
    For Each w In Worksheets
        Set Rng = w.Range("E:E").Find(What:=v.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            If (w.Name = v.Parent.Name And v.Row = Rng.Row And v.Column = Rng.Column) Then
                Set Rng = w.Range("E:E").Find(What:=v.Value, _
                        After:=v, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            End If
            isDup = Not (w.Name = v.Parent.Name And v.Row = Rng.Row And v.Column = Rng.Column)
            If isDup Then
                MsgBox "Duplicate value found at worksheet: [" & w.Name & "], cell (" & Rng.Row & ", " & Rng.Column & ")", vbCritical, "Error"
                Exit Sub
            End If
        Else
            isDup = False
        End If
    Next
End Sub

Open in new window

UniqueExample_b.xlsm
0
 
Ryan ChongCommented:
do you have a sample can upload here so we can try to diagnose the issue?
0
 
iitsAuthor Commented:
0
 
iitsAuthor Commented:
Billiant! Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.