Is it possible to search across entire workbook for duplicates as cell is entered?

[bread]

I am new to Excel and this board, Hello.
I am working on a massive email campaign and am keeping a record of every name that gets mailed. The workbook is multiple sheets long with thousands of names in the A column of each sheet. In order to not email the same person twice, I would like to setup a function that searches for duplicates every time a name gets entered into the name column. As it is now, I manually enter in the name twice, once in the find box, and then in the column if there are no repeats. Ideally when I drag the person's name into the name column, I'd like a row to fill in red to alert me if the name is already in the workbook.
Thanks to anyone that attempts this or has a fix.
Best.

 

[jeffreybrown]

Hi & Welcome to the Board,

You could use this in conditional formatting.

=IF(COUNTIF($A$2:A2,A2)>1,1,0)

Sorry I got in a rush...so therefore an edit...

if you want to highlight both names then use...
=COUNTIF($A$2:$A$100,A2)>1

If just the second name then use...
=COUNTIF($A$2:A2,A2)>1

 

[bread]

Thanks Jeff, I can't seem to search and highlight names in other sheets within the workbook. It works within the active sheet though

 

[jeffreybrown]

You normally can not use conditinal formatting between sheets that I know of so you will probably need some vba.

 

[bread]

If I make a list or the worksheet names in the H column
can i then make something like thisish:
[FONT=Verdana, Arial, Helvetica]=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$7[/FONT]($A$2:$A$100,A2)>1[FONT=Verdana, Arial, Helvetica])) [/FONT]

 

[jeffreybrown]

I surely do not know about that and I have to run right now...

Here is a start on the vba code, but I can't seem to get it to loop through all the worksheets.

Sub DuplicateRows()
Dim cl As Range
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each cl In Range("$A$1:$A$100")
            If Application.WorksheetFunction.CountIf(Range("$A$1:$A$100"), cl) > 1 Then
                cl.Interior.ColorIndex = 40
            End If
        Next cl
    Next ws
End Sub

 

[ravishankar]

HI
try the following codes

Sub Bread()
Dim a As Long, x As Long, y As Long
    For a = 1 To Sheets.Count
        If Worksheets(a).Name <> ActiveSheet.Name Then
        x = Worksheets(a).Range("A65536").End(xlUp).Row
        y = ActiveSheet.Range("A65536").End(xlUp).Row
            If Application.WorksheetFunction.CountIf(Worksheets(a).Range("A1:A" & x), ActiveSheet.Cells(y, 1)) > 1 Then
            ActiveSheet.Cells(y, 1).Interior.ColorIndex = 6
            MsgBox "There is a match in worksheet " & Worksheets(a).Name
            End If
        End If
    Next a
End Sub

It checks the last name of the activesheet against col A of all other sheets.
Ravi