Find duplicates across mulitple worksheets

[Megan_NRC]

Hi, thanks for taking the time to help me! I'm pretty new at <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>, I took a course in college about 6 years ago and I haven't had to use that knowledge since recent and it's not coming back to me as quickly as I hoped.

I'm working on a project tracker for construction. When initially inputting the job number (ex: 0000-0000-0000) I need a vba to search across ALL the worksheets and highlight the duplicate. Ultimately I would like a message box to open with the location of where the duplicate is, but highlighting the 2 cells is good enough.

I have 5 tabs
Maint = Sheet1
NewBuild = Sheet4
Pending = Sheet5
QC = Sheet6
Complete = Sheet7

Thanks for your time! It's much appreciated!

 

[My Aswer Is This]

You said:

When initially inputting the job number (ex: 0000-0000-0000)

Where are you inputting this Job Number?

 

[Megan_NRC]

Column B or "2" whichever way you use haha

 

[My Aswer Is This]

Try this:

Sub Search_For_Job_Number()
Dim c As Range
Dim ans As String
Dim anss As String
Dim ansss As String
ans = InputBox("Enter Job Number")
    For i = 1 To Sheets.Count
        For Each c In Sheets(i).UsedRange
            If c.Value = ans Then
                c.Interior.ColorIndex = 4
                anss = anss & Sheets(i).Name & "  " & c.Address & vbNewLine
            End If
        Next
    Next
    ansss = Replace(anss, "$", "")
MsgBox "The value " & ans & " Was found in below locations" & vbNewLine & ansss
End Sub

 

[My Aswer Is This]

Or try this:
This script will tell you if no value was found.

Sub Search_For_Job_Number()
'Modified 6-2-17 11:51 AM EDT
Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim anss As String
Dim ansss As String
ans = InputBox("Enter Job Number")
    For i = 1 To Sheets.Count
        For Each c In Sheets(i).UsedRange
            If c.Value = ans Then
                c.Interior.ColorIndex = 4
                anss = anss & Sheets(i).Name & "  " & c.Address & vbNewLine
            End If
        Next
    Next
    ansss = Replace(anss, "$", "")
If Len(anss) > 0 Then MsgBox "The value " & ans & " Was found in below locations" & vbNewLine & ansss
If Len(anss) < 1 Then MsgBox "The value " & ans & "  Was not found"
Application.ScreenUpdating = True
End Sub

 

[Megan_NRC]

I don't have an input box popping up.

Not everyone who opens the workbook is going to be inputting jobs. The person inputting the job number is going to manually enter it into the first free row of column B to whichever sheet it is supposed to be. So I'm not sure how to switch this to do the search anytime column B is updated in any sheet.

 

[My Aswer Is This]

That's why I asked earlier where the input would be and you said Column "B" haha.

I'm not sure why you thought my question was funny since in post #1 you never said where the input would be.

So are you saying any time any one enters any value into any cell in column "B" of any sheet you want a script to search for that value in all sheets?

 

[Megan_NRC]

I wasn't laughing at your question, I was simply giggling at the fact that if I said column "B" some would correct me to say "2". I didn't mean to offend you.

Thank you for your time, I have since resolved my problem! Appreciate your help!