Select a range of cells to cpy based on a text searh

oscarlimerick · #1 07-17-2023, 06:46 PM

Hello to the wise and learned folks at the excel forum. So I have attached a sample spreadshet to illustrate what I want to do. The actual spreadsheet I am working on is much larger than this one. As you can see, I have a worksheet with a cell range of A1 to F4. What I wish to do is find a formula that will allow me to select every row that has the text"SAFEWAY" in it, I would prefer the search is not case sensitive. Then after finding all the matches, I want to have excel automatically select not only those cells, but the entire row that cntains this text. In my example all of the matches to "SAFEWAY" are in columns E and F, but in my larger spreadsheet, the match could be in any column. I want any cell that matches "SAFEWAY" or "safeway" to be selected, along wih the entire row that the cell is in, as hilighted in red on my sample spreadsheet. Thanks for your help

Pecoflyer · #2 07-17-2023, 11:21 PM

Hi
are you still using XL2013?

Alansidman · #3 07-18-2023, 01:27 PM

Here is a VBA solution

Code:

Option Explicit

Option Compare Text
Sub Safeway()
    Dim rng As Range, c As Range
    Set rng = Range("B3:F14") 'Change this range to your actual range of data.
    For Each c In rng
        If InStr(c, "Safeway") > 0 Then
            c.EntireRow.Interior.ColorIndex = 6
        End If
    Next c
    MsgBox "Action Completed"
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug)	slaycock	Word VBA	0	02-18-2017 07:00 AM
Dynamically select a TextBox based on range value	IIOII	Word VBA	8	07-20-2016 11:28 PM
Help with Index Function to Select Range of Cells	bretyuin	Excel	1	02-24-2016 05:11 AM
select a cluster of point from a range based on x and y values	sandcharles	Excel	5	02-19-2015 06:15 AM
Color-fill a range of cells, based on text in a different sheet. Possible?	unittwentyfive	Excel	2	06-01-2014 06:48 AM