Copy or move rows that are highlighted based on conditions.

[suzinger]

Hello!

I moved a database from an old DOS-based program to an excel worksheet. I used conditional formatting so that someone can search all cells for any term and the row where that term is found is highlighted in yellow.

I would like to know how to do the following (if it is possible):
*Enter search term
*Rows with term in any cell are highlighted yellow. Table is 217 Rows x 6 Columns.
THEN -
*EITHER - 1. Automatically copy all the highlighted rows to another sheet OR 2. Move the highlighted rows to the top of the table.
*This sort/move will happen each time a new search terms is entered.

The end result I am trying to achieve is that someone can do a search and not have to scroll through the table to find the rows highlighted in yellow. I am open to any suggestions on how to achieve this goal.

Thank you for your time and assistance!

 

[My Aswer Is This]

This script will continue running as long as there are values in column "A" of sheet named "MyData"
The data needs to be in a sheet named "MyData"
It will copy the rows to Sheet named("New"). You will need to create this sheet and name it "New"

Sub Check_Rows()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim c As Range
Dim MyValue As String
MyValue = InputBox("Enter value to search for")
Lastrow = Sheets("MyData").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets("New").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("MyData").Activate
    For i = 1 To Lastrow
        For Each c In Range("A" & i & ":F" & i)
            If c.Value = MyValue Then
                Rows(i).Copy Destination:=Sheets("New").Rows(Lastrowa)
                Rows(i).Cells.Interior.ColorIndex = 6
                Lastrowa = Lastrowa + 1
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[suzinger]

Thank you so much for taking the time to respond!

Exactly how do I utilize this script?

 

[My Aswer Is This]

To install the Macro:
1. Right click on any sheet tab.
2. Choose view code
2. In upper left corner of window choose Insert
3. Choose "Module"
4. Paste the below code in the window.
5. Close the window
To Run the Macro:

Read the instructions in this link. Or if your still need help check back in here.

https://support.office.com/en-us/article/Run-an-Excel-macro-a20bf0b0-5642-4aa8-8b99-2558a6df5fe1

 

[suzinger]

Thank you for the link! I knew how to install the Macro but didn't know how to run it.

Ok, so I made a Test workbook with your Macro. When I run the Macro, a small window opens that reads "Enter value to search for". Nothing happens after I enter my search term and click "Ok".

Do I need to make any changes to my table? Do I need to remove my conditional formatting? Right now my sheet looks like this:

 

[suzinger]

Sorry! guess I don't know how to upload a photo.

 

[My Aswer Is This]

The script continues looking down column "A" till there is no more data in column "A"

It looks for the value you entered into the box.
It's looking in columns "A" to "F" of each row.

It if it did not copy any data to sheet named "New" and did not highlight any rows then it did not find that value in any cell.

Now if you enter "George" and the cell has "George went to the store" then it does not look at that as a find.

Tell me if I'm not understanding you properly.

 

[suzinger]

Hi! I searched for a term that I know is in the table and it did not copy any data to sheet "New" and did not highlight any rows in my table.

Should I remove my conditional formatting first? My conditional formatting highlights all rows in yellow that contain the search term in any cell. My search term is entered into cell B1. I apologize if I was not clear.

 

[suzinger]

Oh I see! So I just re-read your post! It will NOT consider "George went to the store" as a find if I am searching for "George"! That is the problem!

With my conditional formatting I can search for "geo" and "George went to the store" would be a find and that row would be highlighted in yellow.

So, can we modify the script to find my yellow rows and move them to the New sheet?

Thank you so very much!

 

[Michael M]

This should allow for "geo" in a mixed text string.
However, this could cause issues, if you were looking for George and Geography was in the cell. I'd suggest keeping your search as accurate as possible

Sub Check_Rows()
Application.ScreenUpdating = False
Dim i As Long, Lastrow As Long, Lastrowa As Long
Dim MyValue As String, c As Range
MyValue = InputBox("Enter value to search for")
Lastrow = Sheets("MyData").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets("New").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("MyData").Activate
    For i = 1 To Lastrow
        For Each c In Range("A" & i & ":F" & i)
            If InStr(1, c.Value, MyValue, 1) Then
                Rows(i).Copy Destination:=Sheets("New").Rows(Lastrowa)
                Rows(i).Cells.Interior.ColorIndex = 6
                Lastrowa = Lastrowa + 1
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub