You can use the following macro linked to the Workbook_SheetChange event
on the ThisWorkbook tab.

It's a bit slow and can be improved by restricting the search range to
the cells that you are likely to use.

e.g. Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(100, 100)) instead of
Sheet.Cells



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim DuplicatedValue As Boolean
DuplicatedValue = False
For Each Sheet In Sheets
If Sheet Is Sh Then
If Application.CountIf(Sheet.Cells, Target) 1 Then
DuplicatedValue = True
End If
Else
If Application.CountIf(Sheet.Cells, Target) 0 Then
DuplicatedValue = True
End If
End If
Next Sheet
If DuplicatedValue = True Then MsgBox "Duplicate"

End Sub


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=544326