I decided to see if I could make a macro to do this in 6 minutes or less and I think I got the perfect solution for you.
Sub worksheet_duplicates_highlight()
'count the number of worksheets in the workbook
sheet_count = ActiveWorkbook.Worksheets.Count
'loop through the worksheets in the workbook
For a = 1 To sheet_count
'get the number of rows
row_count_a = Sheets(a).Range("A" & Rows.Count).End(xlUp).Row
'loop through the rows in the worksheet
For b = 1 To row_count_a
'get the value for which a duplicate is being searched
original_value = Sheets(a).Cells(b, 1).Value
'loop through the worksheets looking for a duplicate
For c = 1 To sheet_count
'get the number of rows
row_count_c = Sheets(c).Range("A" & Rows.Count).End(xlUp).Row
'loop through the rows in each sheet
For d = 1 To row_count_c
'get the value that is being checked for
check_value = Sheets(c).Cells(d, 1).Value
'make sure this is not the original cell
If a <> c And b <> d Then
If original_value = check_value Then
Sheets(c).Cells(d, 1).Interior.Color = RGB(255, 0, 0)
End If
End If
Next d
Next c
Next b
Next a
End Sub
I included comments in the code as well.
Note that this might take a while in a large spreadsheet and you might want to turn off screen-updating.
No macro needed. A formula will do the trick. Something like this will count the number of records in Sheet2 that match a certain record in Sheet1.
=COUNTIF(Sheet2!A:A,A1)>0
The formula can be modified to include all sheets.