Finding Duplicate Data Across Worksheets

@fred potter
Bravo. Clever macro.

How about a macro? Something like this could work for you:

Sub HiLiteDuplicates()
Dim Cel As Range
Dim Ws As Worksheet

With CreateObject("scripting.dictionary")
For Each Ws In Worksheets
Ws.UsedRange.Interior.ColorIndex = xlNone
For Each Cel In Ws.UsedRange
If Cel.Value <> "" Then
If Not .Exists(Cel.Value) Then
.Add Cel.Value, Cel
Else
Cel.Interior.Color = 456789
.Item(Cel.Value).Interior.Color = 456789
End If
End If
Next Cel
Next Ws
End With
End Sub

COUNTIF does not support a 3D range reference like
    Sheet1:Sheet4!$A:$A
but My Excel Toolbox includes the following function:
    COUNTIF3D(FirstSheetRange, Criteria, [LastSheetRange])
Therefore, the Tip's formula can be replaced by this formula:
    =(COUNTIF3D(Sheet1!$A:$A,A1,Sheet4!$A:$A)-COUNTIF($A:$A,A1))>0
Notice the same formula can be applied to each worksheet's conditional format because it accounts for that sheet's duplicates. In fact, the separate conditional formatting rule that highlights duplicates on a sheet can be deleted if the following formula is used for each worksheet instead:
    =COUNTIF3D(Sheet1!$A:$A,A1,Sheet4!$A:$A)>1
A worksheet’s name must be surrounded by apostrophes if it includes a space or other special character.
To edit a rule's formula in the Conditional Formatting Rules Manager, first click Edit Rule..., then click within the formula and press F2 to enable cursor movement using the arrow keys, Home, End, etc.
For more on 3D range references, see https://excelribbon.tips.net/T011803
And see https://sites.google.com/view/MyExcelToolbox/

You can use PowerQuery to merge the tabs into a single tab, In PQ you can add a new column with the tab name. Then you can use CF to highlight duplicates. But, in PQ you could also simply remove the duplicates if that is what you need to do.