Detecting duplicates over several worksheet or in a workbook

[Newbienew]

I am having a problem with trying to make sure that i do not have any duplicate names on a worksheet as well as on any other worksheet. I have 13 Sheets and if John Doe appears on one of the sheets I would like for it to turn red to indicate that i have a duplicate on other sheet. Is this something that can be accomplished?

 

[DanteAmor]

Try this
Change data in red by your information

Sub Detecting_duplicates()
    Dim sh As Worksheet, sh1 As Worksheet
    Dim c As Range, col As String
    
    Set sh1 = Sheets("[B][COLOR=#ff0000]work[/COLOR][/B]")    'Name of your main worksheet
    col = "[B][COLOR=#ff0000]A[/COLOR][/B]"                   'Column of names
    
    sh1.Range("A:A").Interior.ColorIndex = xlNone
    For Each c In sh1.Range(col & 2, sh1.Range(col & Rows.Count).End(xlUp))
        For Each sh In Sheets
            If sh.Name <> sh1.Name Then
                Set b = sh.Range(col & ":" & col).Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not b Is Nothing Then
                    c.Interior.ColorIndex = 3
                End If
            End If
        Next
    Next
    MsgBox "Done"
End Sub

 

[Newbienew]

I attempted you use the code you have provided but is seem not to have worked out for me.

Added, I have seem not to have provided all the info needed to answer my question fully. I do apologize. The current formula i use is in the conditional formatting. I have to column, lets say c and d. In these columns is the last name (c) and first name (d). The formula will turn the name red if it is duplicated. So if DOE JOHN is entered twice in both columns then it will be flagged in red. I want to be able to use this same technique on all worksheets if the name is on another worksheet. Which in that case I probably wont use read to highlight it maybe orange. I hope this clears things up better.

 

[DanteAmor]

It seems to me that you can not apply the conditional format in different sheets.


If you want, I help you with the code, but you must explain with clear examples what you have, exactly where you have them and what you expect from the result.

 

[Newbienew]

I can use the formula that i placed in conditional format on all pages individually but, not for it to reflect all pages at once. Such as if sheet 1 and sheet 5 have the same name on it I would be hard to tell with out searching for that name, this takes a lot of time.

This ties into my example of looking for a way to achieve this goal. A way to detect or catch a duplicated name among all pages. Each page has two columns, one with the last name and the other column with the first name. The formula that I use in the conditional formatting turns both cells with the last name and the first name red if the name is entered twice. So if doe john is already in column C and D, and then further down the column it shows again it shows in red.

Seeing how the information that will be entered now spread across 12 worksheets. I need a way to make sure that if a name, last name and first name in those columns, are not duplicated on any worksheet or with in the workbook itself. If so I need it to be highlighted to spot it and correct the duplication with out having to search all 12 sheets.

I thought there was a way to do this with a formula in condition formatting but dont think it will cover the whole 12 sheets or workbook.


Added: I had a question about your code above. Just so I know that i am doing it right for the future. Do I cut and paste that in a module, save and that it. That of course adding the name of what I want to be highlighted and column.

when i do i get a bug for this section
Set sh1 = Sheets("work") 'Name of your main worksheet col = "A" 'Column of names
Thank you so much for you help