Highlighting Duplicates across multiple worksheets

**washua** · 01-17-2012, 10:37 AM

Hi, my excel skills aren't all that great and i really need some help with this otherwise i think i may have to go through manually which could take hours!

I have a database for a warehouse full of boxes, there are 12 sheets corresponding to different aisles in the warehouse, each with around 3000 box numbers in their specific locations.

i also have 1 sheet which has a list of 2000 boxes i have to remove from the warehouse.

is there a way (without moving everything to 1 page) that i can highlight all the duplicate numbers in their current sheets so it's easier to locate those boxes that need to be destroyed?

Thanks, josh.

**pike** · 01-18-2012, 03:24 AM

Hi washua

will out the workbook something like

   Dim ws1 As Worksheet, ws2 As Worksheet
    Dim FoundOne As Range, LookInR As Range, LookForR As Range, c As Range
    Dim nr3    As Long, fAddress
    Application.ScreenUpdating = False
    Set ws1 = Sheets("Sheet7")
    Set ws2 = Sheets("Sheet8")

    Set LookInR = ws1.Range("A2:A10")
    Set LookForR = Range(ws2.Range("A2"), ws2.Range("A" & Rows.Count).End(xlUp))

    For Each c In LookForR
        With LookInR
            Set FoundOne = .Find(what:=c, LookAt:=xlPart)
         
            If Not FoundOne Is Nothing Then
                fAddress = FoundOne.Address
              
                Do
                 
                FoundOne.Interior.ColorIndex = 6
                      Set FoundOne = .FindNext(After:=FoundOne)
                Loop While FoundOne.Address <> fAddress
            End If
        End With
    Next c
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set LookInR = Nothing: Set LookForR = Nothing
    Application.ScreenUpdating = True