Hello,

Ozgrid.com has a great find function (http://www.ozgrid.com/forum/showthread.php?t=27240) to return a range of cells using the Find function. You could also adapt that function to instead return a boolean if you only care about whether a duplicate exists (and don't care where the duplicate resides on the sheet). Take a look at the thread above for the Range version. I have adapted the function to return a boolean below. For example, entering:

Find_Dup(Cells(r, 2), Range("B5:B33000"))

would return TRUE if a duplicate value exists and FALSE otherwise. Here is that code:

Function Find_Dup(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Boolean

'Adapted from:
'http://www.ozgrid.com/forum/showthread.php?t=27240

Find_Dup = False

Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False

With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False)
If Not c Is Nothing Then
Find_Dup = True
End If
End With

End Function