click cell and open matching value in next sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...
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 =
Click to expand...
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
Click to expand...
ActiveCell.Activate '
should link to first cell on sheet2 with matching value
and highlight the row of this cell
 
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
 
Back
Top