How do I highlight over multiple sheets

[hsarrategui]

I have a workbook that has a tab for each month. I have tried every google link and formula suggested and nothing seems to be working.

I have a sheet called "January 2023" with contact names in column "A". Every sheet has the same layout. If I wanted "February-December 2023" sheets to highlight a name in their column "A"s to show that said name is in one of the other sheets can I do that?

Example: How do I get "Steven" to highlight in the February sheet because its been entered into the January sheet?
JANUARY 2023 (sheet 1) FEBRUARY 2023(sheet 2)

COLUMN A COLUMN A
Heather Rachel
Chris Steven
Steven Bob

 

[Micron]

Are you looking for one or two names/values at any given attempt? Should not be too hard, but may be a bit slow.
Or are you looking for every Tom, ****, Harry, Susan, etc. etc. that is on more than one sheet at a time?
If the latter and there are hundreds of names then it's something you'd start and then go home for the weekend while it runs (if not on vacation).

 

[DanteAmor]

With conditional formatting.

Perform the following steps:

1. Select the January sheet.
2. Select column A.
3. In the name box write JAN

4. Select the February sheet.
5. Select column A.
6. In the name box write FEB
7. Repeat steps 1 to 3 for all months.
8. Return to the January sheet.
9. Select the cells in column A where you want to apply conditional formatting, for example cell A1 to A10.
10. Insert a conditional formatting rule
11. Put the following formula:

=OR(COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

12. Repeat steps 8 to 11 for all months.

Here I leave you the formulas for each sheet:

=OR(COUNTIF(jan,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(nov,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(dec,$A1))

=OR(COUNTIF(jan,$A1),COUNTIF(feb,$A1),COUNTIF(mar,$A1),COUNTIF(apr,$A1),COUNTIF(may,$A1),COUNTIF(jun,$A1),COUNTIF(jul,$A1),COUNTIF(aug,$A1),COUNTIF(sep,$A1),COUNTIF(oct,$A1),COUNTIF(nov,$A1))

I also share my test file with you so you can review the range names and formulas in case you have any questions.

Loading Google Sheets

Another alternative is to create a macro that searches for each sheet name across all sheets.
Tell me if you want the macro.

I hope to hear from you soon.
Respectfully
Dante Amor
_______ _____

 

[Micron]

Or are you looking for every Tom, ****

Seriously? You can't write the short form for Richard?

 

[kevin9999]

I threw this together just for fun. Try it out on a copy of your workbook. Took around 0.6 secs with 12 sheets x 1,000 random names on each. @DanteAmor 's solution is probably simpler
Assumes row 1 is a header row.

Option Explicit
Sub HiLiteDupes()
    Application.ScreenUpdating = False
    Dim d As Object, data(), allData(), a, rng As Range, arr
    Dim i As Long, j As Long, k As Long, m As Long, n As Long, totRows As Long, r As Long, rw As Long
    Set d = CreateObject("scripting.dictionary")
    
    For i = 1 To Worksheets.Count
        ReDim data(1 To Worksheets.Count - 1)
        totRows = 0
        Worksheets(i).Columns("A").Interior.Color = xlNone
        a = Worksheets(i).Range("A2", Worksheets(i).Cells(Rows.Count, "A").End(xlUp))
        k = 1
        For j = 1 To Worksheets.Count
            If j <> i Then
                Set rng = Worksheets(j).Range("A2", Worksheets(j).Cells(Rows.Count, "A").End(xlUp))
                If Application.CountA(rng) > 0 Then
                    data(k) = rng.Value
                    totRows = totRows + UBound(data(k), 1)
                    k = k + 1
                End If
            End If
        Next j
        ReDim allData(1 To totRows, 1 To 1)
        r = 1
        For k = 1 To Worksheets.Count - 1
            If Not IsEmpty(data(k)) Then
                arr = data(k)
                For rw = 1 To UBound(arr, 1)
                    allData(r, 1) = arr(rw, 1)
                    r = r + 1
                Next rw
            End If
        Next k
        For m = 1 To UBound(allData, 1)
            d(allData(m, 1)) = 1
        Next m
        For n = 1 To UBound(a, 1)
            If d.exists(a(n, 1)) Then Worksheets(i).Cells(n + 1, 1).Interior.Color = vbYellow
        Next n
        d.RemoveAll
        Erase a
        Erase data()
        Erase allData()
        Erase arr
    Next i
    Application.ScreenUpdating = True
End Sub