vba to select rows in one sheet based on a cell value in another sheet

[cjcass]

Hi,

I'm looking for some vba that will take the value in the selected cell (variable) in Sheet1 and then select all the rows in Sheet2 that contain exactly the same value in Column J Sheet2. So if Sheet1 cell N23 is selected and it contains the words 'tree stump' the vba will select all the rows in Sheet2 that have the words 'tree stump' in Column J. Would it also then be possible for the uppermost selected row in Sheet2 to be at the top of the display on the screen?

Any help much appreciated.

 

[JoeMo]

Untested. Note the comment regarding the lookat argument in the range.find method.

Sub SelectIfMatch()
'run this code from Sheet1
Dim Sh1 As Worksheet, Sh2 As Worksheet, Lookfor As Variant, Fnd As Variant, R As Range, fAdr As String
Set Sh1 = Sheets("Sheet1"): Set Sh2 = Sheets("Sheet2")
Lookfor = Selection.Value
'on next line, change lookat to xlWhole to find only those cells that contain the entire string
'in the activecell on Sheet1
Set Fnd = Sh2.Range("J:J").Find(what:=Lookfor, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not Fnd Is Nothing Then
    fAdr = Fnd.Address
    Do
        If R Is Nothing Then
            Set R = Fnd
        Else
            Set R = Union(R, Fnd)
        End If
        Set Fnd = Sh2.Range("J:J").FindNext(Fnd)
        If Fnd Is Nothing Then Exit Do
        If Fnd.Address = fAdr Then Exit Do
    Loop
End If
If Not R Is Nothing Then
    Sh2.Activate
    R.Select
Else
    MsgBox "Value of selected cell on Sheet1 is not found in col J of Sheet2"
End If
End Sub

 

[cjcass]

Untested. Note the comment regarding the lookat argument in the range.find method.

Sub SelectIfMatch()
'run this code from Sheet1
Dim Sh1 As Worksheet, Sh2 As Worksheet, Lookfor As Variant, Fnd As Variant, R As Range, fAdr As String
Set Sh1 = Sheets("Sheet1"): Set Sh2 = Sheets("Sheet2")
Lookfor = Selection.Value
'on next line, change lookat to xlWhole to find only those cells that contain the entire string
'in the activecell on Sheet1
Set Fnd = Sh2.Range("J:J").Find(what:=Lookfor, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not Fnd Is Nothing Then
    fAdr = Fnd.Address
    Do
        If R Is Nothing Then
            Set R = Fnd
        Else
            Set R = Union(R, Fnd)
        End If
        Set Fnd = Sh2.Range("J:J").FindNext(Fnd)
        If Fnd Is Nothing Then Exit Do
        If Fnd.Address = fAdr Then Exit Do
    Loop
End If
If Not R Is Nothing Then
    Sh2.Activate
    R.Select
Else
    MsgBox "Value of selected cell on Sheet1 is not found in col J of Sheet2"
End If
End Sub

Hi,
Thanks very much for this code and your time - it works great and highlights the cells that contain the value but is it possible to highlight the complete rows?
It's not the end of the world if it can't be done, it would just save me a bit if extra time.
Thanks again.

 

[Alex Blakenburg]

Try changing

R.Select

To

R.EntireRow.Select

 

[cjcass]

Try changing

R.Select

To

R.EntireRow.Select

Awesome! works a treat.
Many thanks indeed for your time and the solution

 

[Alex Blakenburg]

It's @JoeMo's solution but we are glad we could help. Thanks for the feedback.

 

[cjcass]

It's @JoeMo's solution but we are glad we could help. Thanks for the feedback.

Oh yes, I didn't clock it was someone different responding - thanks JoeMo!!

 

[JoeMo]

Oh yes, I didn't clock it was someone different responding - thanks JoeMo!!

You are welcome - thanks for the reply. And, thanks to Alex for the assist.

 

[Gaurangg]

I am also looking for same however the twist is that I have number in Sheet1 and want to select the number of rows from sheet2. Means, if Sheet1 cell value F2 = 3 then 3 rows from Sheet2 should be selected. I have also post my thread for this. Please if anyone can check.

Macro to assign cases based on numbers mentioned in cell