Macro help, look for value, highlight row

[mlibrett]

Hi,

I am trying to use VBA to create a macro that will look down a certain column for specified values, and then highlight the entire row if they have those values and copy/paste them into another sheet.

Anyone have any ideas?

Thanks

 

[HalfAce]

Hi, not much to go on here so see if this helps.
Assuming your certain column (to search through) were column A, and that you're willing to enter the value to search for in an inputbox, and the other sheet you want these rows copied to is sheet2, then perhaps you can make something like this work for you.

Sub Demo()
Dim SearchVal As String, LstRw As Long, i As Long
SearchVal = InputBox("Enter the value to search for.", "SEARCH FOR:")
If Len(SearchVal) = 0 Then Exit Sub
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LstRw
  If Cells(i, "A").Value = SearchVal Then _
    Rows(i).EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2)
Next
End Sub

Hope it helps.

 

[mlibrett]

Thats great thanks!

Say if I didnt want an input box to pop up, can I just make the searchval= my specific values in quotes?

 

[jeffreybrown]

Here is my effort...

Looking in column A for Closed, but this just highlights

Sub Colours()
    Const SR As Long = 2
    Const sCol As String = "A"
    Dim i As Long
    Dim LR As Long: LR = Range(sCol & Rows.Count).End(xlUp).Row
    For i = SR To LR
        Select Case Range(sCol & i).Value
        Case "Closed": Rows(i).Interior.ColorIndex = 5
        End Select
    Next i
End Sub

 

[mlibrett]

Anyone else have any input towards this? I'm looking to have the macro look down a specified column for 4 different values (something like A, B, C, D) and if those values are there for it to highlight and copy the rows

 

[HalfAce]

Hey, sorry for the delay. Been a few days since I logged on last.

I'm looking to have the macro look down a specified column for 4 different values (something like A, B, C, D) and if those values are there for it to highlight and copy the rows

Here's something you might try:

Sub FindAndCopyDemo()
Dim LstRw As Long, i As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LstRw
  Select Case Cells(i, "A").Value
    Case "A", "B", "C", "D"
      Rows(i).EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2)
  End Select
Next i
End Sub

You can then just change the line: Case "A", "B", "C", "D", replacing A, B, C & D with the values you're really looking for.

Hope it helps.