Checking multiple sheets in Excel

Grassland79 
"Is there a way to change the location of your 'Run' button to S1"
> Just right click the 'Run' Button and you will be able to move it by click holding the border of that button and dragging it to cell S1 and you can resize it.
"change where you display the found data to maybe S1 - T6"
> First move the list of sheet names from column A to column S (S1:S6)
> here's the updated macro script:

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 R1 in the 2024 sheet
    searchValue = Worksheets("2024").Range("R1").Value

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

    ' Clear the previous cell addresses in column T
    Worksheets("2024").Range("T2:T" & Worksheets("2024").Cells(Rows.Count, "T").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

It will use your cell R1 for the lookup/search value,
once you move the sheet names from Column A (A3:A8) to Column S (S1:S6),
and for the cell referencing address it will be written in column T.