Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

I have three ranges H5:H38, L5:L24, P5:P14. I would like to enter a text value in P3 and…

  1. User

    I have three ranges H5:H38, L5:L24, P5:P14. I would like to enter a text value in P3 and have a VBA code that will search my ranges and highlight any cell that contains the text value in P3. Is it possible to do this

  2. Assistant

    Yes, it is possible to achieve this using VBA code. Here's an example code that you can use:

    Sub HighlightCells()
        Dim searchValue As String
        Dim cell As Range
        
        ' Get the search value from cell P3
        searchValue = Range("P3").Value
        
        ' Clear any previous highlighted cells
        Range("H5:H38, L5:L24, P5:P14").Interior.Pattern = xlNone
        
        ' Loop through each cell in the specified ranges
        For Each cell In Range("H5:H38, L5:L24, P5:P14")
            ' Check if the cell contains the search value
            If InStr(cell.Value, searchValue) > 0 Then
                ' Highlight the cell
                cell.Interior.Color = RGB(255, 255, 0) ' Yellow
            End If
        Next cell
    End Sub
    

    To use this code, open the VBA editor in Excel by pressing Alt+F11, then insert a new module. Copy and paste the above code into the module. Now, whenever you enter a text value in cell P3, you can run the HighlightCells macro to search and highlight the cells in the specified ranges that contain the entered text value.

  3. User

    The code works fine, but the search text in P3 has to be exactly like the text within the range. For example if I enter fire in P3 it does not highlight the cells that contain the word Fire. Can the code disregard capital letters

  4. Assistant

    Certainly! To disregard capital letters when comparing the search text with the text in the ranges, you can convert both strings to lowercase using the LCase function. Here's the modified code:

    Sub HighlightCells()
        Dim searchValue As String
        Dim cell As Range
        
        ' Get the search value from cell P3 and convert to lowercase
        searchValue = LCase(Range("P3").Value)
        
        ' Clear any previous highlighted cells
        Range("H5:H38, L5:L24, P5:P14").Interior.Pattern = xlNone
        
        ' Loop through each cell in the specified ranges
        For Each cell In Range("H5:H38, L5:L24, P5:P14")
            ' Check if the lowercase version of the cell's value contains the search value
            If InStr(LCase(cell.Value), searchValue) > 0 Then
                ' Highlight the cell
                cell.Interior.Color = RGB(255, 255, 0) ' Yellow
            End If
        Next cell
    End Sub
    

    With this modification, the search will be case-insensitive and will highlight cells regardless of whether the text in the ranges is in uppercase or lowercase.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.