The first thing I would do is take a moment to consider what range of cells among the sheets is likely to have data entered in it. On some sheets it might be A1:A10. On other sheets it might be B5:B20. The idea is, instead of evaluating all 16+ million cells on all sheets, only evaluate the range among all the sheets that would encompass the possible range on each sheet. That way, the workbook does not slow down to a crawl while the entire sheets are needlessly searched.
Let's say you determine that no sheet will have anything entered in it outside of the range A1:B20 (modify for whatever that range turns out to be for you).
Place this in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ws As Worksheet, EvalRange As Range
Set EvalRange = Range("A1:B20")
If Intersect(Target, EvalRange) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
'First, evaluate the parent sheet
If WorksheetFunction.CountIf(EvalRange, Target.Value) > 1 Then
MsgBox Target.Value & " already exists on this sheet."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
'Next, evaluate the other workbook sheets
For Each ws In Worksheets
With ws
If .Name <> Target.Parent.Name Then
If WorksheetFunction.CountIf(Sheets(.Name).Range("A1:B20"), Target.Value) > 0 Then
MsgBox Target.Value & " already exists on the sheet named " & .Name & ".", _
16, "No duplicates allowed in " & EvalRange.Address(0, 0) & "."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit For
End If
End If
End With
Next ws
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Come to think of it, Find would probably be quicker than CountIf, but first let's see if this gets you closer to what you are trying to achieve, and it can be adjusted for speed later if need be.
