Checking multiple sheets in Excel

Grassland79
Copper Contributor
Feb 05, 2024
Perfect. Thanks so much!

Iron Contributor

Feb 05, 2024

Grassland79
updated code, instead of searching your EXACT lookup value, it will now search that contains your lookup value, either of them that has a strikethrough or none.

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 contains the search value as a substring (case-insensitive)
                    If InStr(1, cell.Value, searchValue, vbTextCompare) > 0 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

sample:

result:

Grassland79
Copper Contributor
Feb 03, 2024
Hi again. I just came across one other scenario that I was wondering about. Say in sheet 2022, I had two names, one an employee who was replaced and the other the name of his replacement. So, cell A1 might have the names Mike Mitchell and Andy Frye, with Mike Mitchell having a strikethrough to indicate he was no longer with the company. Currently, if I do a search for either Mike Mitchell or Andy Frye, it won't find them, presumably because the cell contains more than the one name I am entering in the search. Is it possible for the search to find either name in this scenario?
Grassland79
Copper Contributor
Feb 02, 2024
Great, thanks again for everything.

Rodrigo_

Iron Contributor

Feb 01, 2024

Grassland79

try this:

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

2022 sheet:

2019 sheet:

2024 sheet (result):

Grassland79
Copper Contributor
Jan 31, 2024
Thanks. So, I wanted it to highlight those cells in prior years if there was a match whether or not they had a strikethrough. For example, if I enter Joe Smith in R1 on sheet 2024 and click on the lookup button, if Joe Smith is in 2022 with no strikethrough, it would get highlighted. If Joe Smith was also in the 2023 sheet but had s strikethrough for that year, that should also get highlighted. So both the Joe Smith's (one with a strikethrough and one without) would get highlighted. Right now, it appears that both conditions (the name match and the strikethrough) have to be met for it to be highlighted since only the Joe Smith with the strikethrough is highlighted. Sorry to be a pain and, again, if it's too much of a bother, don't worry about it. Thanks so much for everything!

Rodrigo_

Iron Contributor

Jan 31, 2024

Grassland79
here's the updated code with the modification to highlight the cells that both contain the search value and either have strikethrough text or not:

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 and the cell's text is struck through
                    If cell.Value = searchValue And cell.Font.Strikethrough 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

Grassland79
Copper Contributor
Jan 31, 2024
Hi again. One other question: Some of my data in prior year cells is formatted with a strikethrough (to indicate someone who is no longer with the company). However, those cells are not found with the search, even if the name matches (I assume because of the strikethrough). Is there a way for the program to find a name regardless of whether it has a strikethrough or not? Not a big deal if it's too difficult but I thought I'd ask in case it's an easy fix. Thanks again!
Grassland79
Copper Contributor
Jan 30, 2024
Thanks so much for all your help!

Forum Discussion

Resources