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.

 

[gray_b]

Having googled the error code. It seems it might not like some of the cell having #N/A in them. What do you think?

 

[DanteAmor]

It is correct, you have cells with errors, you correct cells with errors, or modify the macro, but it is impossible for a macro to incorporate all the possibilities you have in your book (protected sheets, hidden sheets, cells with errors, merged cells, etc etc)
You can fix your formula with IfError ()

Try next:

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)
[COLOR=#0000ff]                If Not IsError(d.Value) Then[/COLOR]
                    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
                End If
            Next
        End If
    Next
End Sub

 

[gray_b]

It works. Thats is absolutely brillant. I would never have sorted it out.

A huge big thankyou for your time.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.