Need guidance on finding duplicate data in a multisheet workbook

[gray_b]

I have a 25 sheet workbook, that I need to highlight duplicates in only 1 column (the same column) in each sheet. Each sheet has another 15 columns of other data. The data is a 4 digit number. But maybe changing that to a string of text in the future.

I have tried using conditional formatting, but this is not reliable over multi sheets. It works in a limited way, but it does not pick up all duplicates.

I have tried 'record macro' in createing conditional formating, but it does not record anything. Was hoping on using the code, and inserting it into each worksheet.

I have scoured google, and I have tried numerous vba code snippets. But again nothing fits my requirements.

I need guidance on creating a suitable macro that uses "Private Sub Workbook_Open()"

Any advice and guidance please.

 

[DanteAmor]

Can you explain what you need?

 

[gray_b]

Sorry I missed a vital word. I should have said "How can I get it, so it does NOT use the first worksheet".

As my 1st sheet is my main data page, which is an imported database via an ADODB.Connection

As searching for duplicates highlights everything, as everything appears on the 1st worksheet (the main database).

 

[DanteAmor]

Try this

Change "initial" by the name of your main sheet.

Private Sub Workbook_Open()    

    Dim sh As Worksheet, sh2 As Worksheet, c As String, d As Range, b As Range
    c = "B"
    
    For Each sh In Sheets
        sh.Range(c & ":" & c).Interior.ColorIndex = xlNone
    Next
    '
    For Each sh In Sheets
        if sh.name <> "[COLOR=#ff0000]Initial[/COLOR]" then
        For Each d In sh.Range(c & "1", sh.Range(c & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 23)
            If d.Interior.ColorIndex = xlNone Then
                For Each sh2 In Sheets
                    If sh2.Name <> sh.Name Then
                        Set b = sh2.Range(c & ":" & c).Find(d.Value, LookIn:=xlValues, lookat:=xlWhole)
                        If Not b Is Nothing Then
                            d.Interior.ColorIndex = 6
                            b.Interior.ColorIndex = 6
                        End If
                    End If
                Next
            End If
        Next
        end if
    Next
End Sub

 

[gray_b]

Hi - It misses the 1st worksheet, which is good.

And it goes through the next worksheets, but it does not highlight anything.

 

[DanteAmor]

Sorry, try this
Change "initial" by the name of your main sheet.

Private Sub Workbook_Open()    


    Dim sh As Worksheet, sh2 As Worksheet, c As String, d As Range, b As Range
    c = "B"
    
    For Each sh In Sheets
        sh.Range(c & ":" & c).Interior.ColorIndex = xlNone
    Next
    '
    For Each sh In Sheets
        
        For Each d In sh.Range(c & "1", sh.Range(c & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 23)
            If d.Interior.ColorIndex = xlNone Then
                For Each sh2 In Sheets
                    If sh2.Name <> sh.Name Then
                        Set b = sh2.Range(c & ":" & c).Find(d.Value, LookIn:=xlValues, lookat:=xlWhole)
                        If Not b Is Nothing Then
                            d.Interior.ColorIndex = 6
                            if b.parent.name <> "[COLOR=#ff0000]Initial[/COLOR]" then
                               b.Interior.ColorIndex = 6
                            end if
                        End If
                    End If
                Next
            End If
        Next
       
    Next
End Sub

 

[gray_b]

Hi - I thankyou for your support, but it still does not work.

I do not want the code to look at the first sheet at all, or do anything to it.

Somehow I need the 'For Each sh In Sheets' to start off at sh + 1

I am also not sure if it has a bearing. But from sheet 2 onwards, the column I looking for duplicates in. Looks at data in the 1st sheet via the use of for example =VLOOKUP(A55,mdb!$A$1:$W$7300,3,0)

 

[DanteAmor]

I do not want the code to look at the first sheet at all, or do anything to it.

That is important to explain from the beginning

But no problem, here the macro reviews all the sheets except the first sheet of your file:

Private Sub Workbook_Open()
    Dim sh As Worksheet, sh2 As Worksheet, c As String, d As Range, b As Range
    c = "B"
    
    For Each sh In Sheets
        If sh.Index > 1 Then
            sh.Range(c & ":" & c).Interior.ColorIndex = xlNone
        End If
    Next
    '
    For Each sh In Sheets
        If sh.Index > 1 Then
            For Each d In sh.Range(c & "1", sh.Range(c & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 23)
                If d.Interior.ColorIndex = xlNone Then
                    For Each sh2 In Sheets
                        If sh2.Index > 1 Then
                            If sh.Name <> sh2.Name Then
                                Set b = sh2.Range(c & ":" & c).Find(d.Value, LookIn:=xlValues, lookat:=xlWhole)
                                If Not b Is Nothing Then
                                    d.Interior.ColorIndex = 6
                                    b.Interior.ColorIndex = 6
                                End If
                            End If
                        End If
                    Next
                End If
            Next
        End If
    Next
End Sub

 

[gray_b]

Hi - In implementing the code for c = "C", it does not highlight anything. Apart from on every single sheet the heading 'variety' is highlighted, this shows code is working for column C. But in adding the code twice for c = "C" and then for c = "A" it works nearly OK for for c = "A"

Please checkout images below. It shows as an example one item being highlighted as duplicated on column A, but not in column C (which it should). I cannot get my head around that. Apart from what I mentioned previously that typically all cells in column C are generated from a formulae, as example =VLOOKUP(A7,mdb!$A$1:$W$7300,3,0). Sheet 'mdb' is the very 1st sheet with all the data in.

and

Also in image below, it shows that on one sheet, there is a duplicated cell, but it is not highlighted.

Cheers

 

[DanteAmor]

Ok, try this.

Private Sub Workbook_Open()
    Dim sh As Worksheet, sh2 As Worksheet, c As String, d As Range, b As Range
    Dim lr As Long, celda As String, inicial As String, r As Range
    c = "C"
    
    For Each sh In Sheets
        If sh.Index > 1 Then
            sh.Range(c & ":" & c).Interior.ColorIndex = xlNone
        End If
    Next
    '
    For Each sh In Sheets
        If sh.Index > 1 Then
            lr = sh.Range(c & Rows.Count).End(xlUp).Row
            For Each d In sh.Range(c & "3:" & c & lr) '.SpecialCells(xlCellTypeConstants, 23)
                If d.Value <> "" Then
                    inicial = sh.Name & d.Address
                    If d.Interior.ColorIndex = xlNone Then
                        For Each sh2 In Sheets
                            If sh2.Index > 1 Then
                                
                                Set r = sh2.Columns(c)
                                Set b = r.Find(d.Value, LookAt:=xlWhole, LookIn:=xlValues)
                                If Not b Is Nothing Then
                                    celda = b.Address
                                    Do
                                        If inicial <> sh2.Name & b.Address Then
                                            d.Interior.ColorIndex = 6
                                            b.Interior.ColorIndex = 6
                                        End If
                                        Set b = r.FindNext(b)
                                    Loop While Not b Is Nothing And b.Address <> celda
                                End If
                                
                            End If
                        Next
                    End If
                End If
            Next
        End If
    Next
End Sub

 

[gray_b]

It looks like almost there. Column A highlights duplicates, but part way through highlighting column C it throws an error.

See image below. Intially a VB run time error 13, then a Excel error 2042