selecting a cell in sheet1

[paul mueller]

that will then take you to a cell on sheet2 with the same value and
hightlight the row the cell is on.
I can't seem to figure out how to only be able to use range e1:e20 to cause
the sub to start but still allow other cells to be changed without causing
the sub to start.
Also i can get the active cell to match and get to sheet2, but cannot figure
out how to select the matching cell on sheet2 and highlight the row the
matching cell is on.
This is what i have so far.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim selection, val
selection = ActiveCell 'should be sheet1
range e1:e20
val = Worksheets("sheet2").Range("h1:h20")
For Each cell In val
If cell = selection Then
Worksheets("sheet2").Activate 'should activate
matching cell and highlight row

End If
Next
End Sub


Any help would be greatly appreciated.

Paul

 

[Bernie Deitrick]

Paul,

Try the event code below, copied into Sheet1's codemodule.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim varSel As Variant
Dim rngVal As Range
Dim myCell As Range

If Target.Cells.Count <> 1 Then Exit Sub
varSel = ActiveCell.Value
If Not Intersect(Target, Range("E1:E20")) Is Nothing Then
Worksheets("Sheet2").Activate
On Error GoTo NotFound:
Set rngVal = Worksheets("Sheet2").Range("H1:H20").Find(varSel)
rngVal.EntireRow.Select
rngVal.Activate
End If
Exit Sub
NotFound:
Worksheets("Sheet1").Activate
MsgBox "The value " & varSel & " was not found on Sheet2"
End Sub