DUPLICATECELLS

DUPLICATECELLS(rgeValues, bAsArray)
Returns the cell addresses of all the items that appear more than once.

REMARKS
Public Function DUPLICATECELLS( _

Public Function DUPLICATECELLS( _ 
         ByVal rgeValues As Range, _ 
Optional ByVal bAsArray As Boolean = False) _ 
         As Variant 

Dim lcellcount As Long 
Dim rgeCell As Range 
Dim lmatchindex As Long 
Dim sduplicateaddresses As String 
Dim aTheDuplicates As Variant 

    For lcellcount = 1 To rgeValues.Cells.Count 
       Set rgeCell = rgeValues.Cells(lcellcount) 
    
       If (rgeCell.Value <> "") Then 
      
          lmatchindex = Application.WorksheetFunction.Match(rgeCell, rgeValues, 0) 
          
          If (lmatchindex <> lcellcount) Then 
             sduplicateaddresses = sduplicateaddresses & "," & rgeCell.Address(False, False) 
          End If 
       End If 
    Next 

   If (Len(sduplicateaddresses) = 0) Then 
      DUPLICATECELLS = "no duplicates" 
   Else 
      sduplicateaddresses = Right(sduplicateaddresses, Len(sduplicateaddresses) - 1) 
   End If 

   If (bAsArray = False) Then 
      DUPLICATECELLS = sduplicateaddresses 
   Else 
      aTheDuplicates = VBA.Split(sduplicateaddresses, ",") 
      aTheDuplicates = Application.WorksheetFunction.Transpose(aTheDuplicates) 
      DUPLICATECELLS = aTheDuplicates 
   End If 
       
End Function 

For instructions on how to add this function to a workbook refer to the page under Inserting Functions