Checking multiple sheets in Excel

Hello Grassland79 

Macro will do your query.  I've made a sample file based on your requirements and added some suggestions. as you can see on the image below, I've inserted a button in order to run the macro that will look for your lookup value in B1, and since I added your sheet names on the list, it will also highlight in what certain sheet names your lookup value has been found and added a comment for the referencing (column B)

Sub CheckForMatch()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim searchValue As String
    Dim sheetList As Range
    Dim sheetCell As Range

    ' Get the value from cell B1 in the 2024 sheet
    searchValue = Worksheets("2024").Range("B1").Value

    ' Get the list of sheet names
    Set sheetList = Worksheets("2024").Range("A4:A" & Worksheets("2024").Cells(Rows.Count, "A").End(xlUp).Row)

    ' Clear the previous cell addresses in column B
    Worksheets("2024").Range("B4:B" & Worksheets("2024").Cells(Rows.Count, "B").End(xlUp).Row).ClearContents

    ' Check if the search value is not an empty string
    If searchValue <> "" Then
        ' Loop through each worksheet
        For Each ws In ThisWorkbook.Worksheets
            ' Clear all cell highlighting in the worksheet applied by this code
            For Each cell In ws.UsedRange
                If cell.Interior.Color = RGB(255, 255, 0) Then ' Yellow
                    cell.Interior.ColorIndex = 0
                End If
            Next cell

            ' Skip the 2024 sheet
            If ws.Name <> "2024" Then
                ' Loop through each cell in the worksheet
                For Each cell In ws.UsedRange
                    ' If the cell's value matches the search value
                    If cell.Value = searchValue Then
                        ' Highlight the cell
                        cell.Interior.Color = RGB(255, 255, 0) ' Yellow

                        ' Highlight the sheet name in the list and add cell address
                        For Each sheetCell In sheetList
                            If sheetCell.Value = ws.Name Then
                                sheetCell.Interior.Color = RGB(255, 255, 0) ' Yellow
                                sheetCell.Offset(0, 1).Value = "Found in " & cell.Address
                            End If
                        Next sheetCell
                    End If
                Next cell
            End If
        Next ws
    End If
End Sub

Note: Unfortunately, if you have some highlighted cells that are the same with the macro (specifically Yellow or RGB (255, 255, 0)), VBA does not provide a straightforward way to distinguish between highlights applied by code and those applied manually. as a workaround, you could use a specific color for the highlights applied by this code, and then modify the code to only clear cells with that specific color.