I have three ranges H5:H38, L5:L24, P5:P14. I would like to enter a text value in P3 and…
- 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
- 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 SubTo 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
HighlightCellsmacro to search and highlight the cells in the specified ranges that contain the entered text value. - 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
- 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
LCasefunction. 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 SubWith 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.