click cell and open matching value in next sheet
 

I am having problems with the code below, I just can't seem to get it to work...I tried using range and cell for selection and for the then statement but kept getting errors

Private Sub Worksheet_SelectionChange(ByVal Target As Range
Dim selection, val, cel
selection = ActiveCell ' should be sheet1 with range e1:e20 the only cells able to cause this to star
val = worksheets("sheet2").Range("e13:e200"
For Each cell In va
If cell = selection The
ActiveCell.Activate ' should link to first cell on sheet2 with matching value and highlight the row of this cel
worksheets("sheet2").Activat
End I
Nex
End Su

Any help would be greatly appreciated
Thank
Paul

click cell and open matching value in next sheet
 

Is this what you're looking for ?

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Dim TableRng As Range, C As Range
Set TableRng = Sheets("Sheet2").Range("E13:E200")
For Each C In TableRng
C.EntireRow.Interior.ColorIndex = xlNone
If C.Value = Target.Value Then
Sheets("Sheet2").Activate
C.EntireRow.Interior.ColorIndex = 6
End If
Next
End Sub

Regards,
Greg
-----Original Message-----
I am having problems with the code below, I just can't
seem to get it to work...I tried using range and cell for
selection and for the then statement but kept getting
errors.

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Dim selection, val, cell
selection =
ActiveCell ' should
be sheet1 with range e1:e20 the only cells able to cause
this to start
val = worksheets("sheet2").Range("e13:e200")
For Each cell In val
If cell = selection Then

ActiveCell.Activate '
should link to first cell on sheet2 with matching value
and highlight the row of this cell
worksheets("sheet2").Activate
End If
Next
End Sub

Any help would be greatly appreciated.
Thanks
Paul
.

click cell and open matching value in next sheet
 

Follow-up on my post:

You'll probably also want to scroll to the row that is
highlighted. I include code to do this and to correct the
bug if the user selects more than one cell.

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Dim TableRng As Range, C As Range
On Error Resume Next
Set TableRng = Sheets("Sheet2").Range("E13:E200")
For Each C In TableRng
If Target.Count = 1 Then
C.EntireRow.Interior.ColorIndex = xlNone
If C.Value = Target.Value Then
Sheets("Sheet2").Activate
ActiveWindow.ScrollRow = C.Row - 5
C.EntireRow.Interior.ColorIndex = 6
End If
End If
Next
End Sub

Regards,
Greg



-----Original Message-----
Is this what you're looking for ?

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Dim TableRng As Range, C As Range
Set TableRng = Sheets("Sheet2").Range("E13:E200")
For Each C In TableRng
C.EntireRow.Interior.ColorIndex = xlNone
If C.Value = Target.Value Then
Sheets("Sheet2").Activate
C.EntireRow.Interior.ColorIndex = 6
End If
Next
End Sub

Regards,
Greg
-----Original Message-----
I am having problems with the code below, I just can't
seem to get it to work...I tried using range and cell for
selection and for the then statement but kept getting
errors.

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Dim selection, val, cell
selection =
ActiveCell ' should
be sheet1 with range e1:e20 the only cells able to cause
this to start
val = worksheets("sheet2").Range("e13:e200")
For Each cell In val
If cell = selection Then

ActiveCell.Activate '
should link to first cell on sheet2 with matching value
and highlight the row of this cell
worksheets("sheet2").Activate
End If
Next
End Sub

Any help would be greatly appreciated.
Thanks
Paul
.

.