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!

 

[suzinger]

Yes, I just tested it and I am still getting duplicates whenever the row has the search term in more than one cell.

Thank you so much for all your hard work!

 

[Michael M]

Ok, a slight modification...

Sub Check_Rows()
Application.ScreenUpdating = False
Dim i As Long, Lastrow As Long, Lastrowa As Long
Dim MyValue As String, c As Integer, x As Integer
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
x = 0
For i = 1 To Lastrow
       For c = 1 To 6
            If InStr(1, Cells(i, c).Value, MyValue, 1) Then
                Rows(i).Copy Destination:=Sheets("New").Rows(Lastrowa)
                Rows(i).Cells.Interior.ColorIndex = 6
                Lastrowa = Lastrowa + 1
                x = x + 1
            End If
            If x = 1 Then Exit For
        Next c
    x = 0
    Next i
Application.ScreenUpdating = True
End Sub

 

[suzinger]

This is perfect Michael M! You are the BEST!

I only had to make a slight modification to have the program end on the New sheet (with the search results). This is a very big help! Thank you so very much!

 

[My Aswer Is This]

Glad to see it's working. I believe Michael modified the code I wrote but glad he joined in here and helped you.

This is perfect Michael M! You are the BEST!

I only had to make a slight modification to have the program end on the New sheet (with the search results). This is a very big help! Thank you so very much!

 

[suzinger]

Thank YOU 'My Aswer Is This"!!! I sincerely appreciate your precious time and assistance!
Have a wonderful weekend!

 

[My Aswer Is This]

You too suzinger. Wishing you the best.

Thank YOU 'My Aswer Is This"!!! I sincerely appreciate your precious time and assistance!
Have a wonderful weekend!