Auto-jump to another cell

[Ellen G]

Hi there --

I have a color coded key at the top of my spreadsheet. One cell is blue, one
cell is red, etc. I would like the user to be able to click on one of those
cells and automatically jump to the appropriate section in the spreadsheet. I
saw another posting with the following code using validation:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.Offset(1, 0).Select
End Sub

I was able to make this work by inserting something into the color-coded
cell. However, I have 5 color-coded cells and each needs to jump to a
different section of the spreadsheet.

So I have a couple of questions:

1. First, can I automatically jump simply by clicking on the cell, without
having to insert something?
2. How do I insert code for each individual jump?

If you are able to provide code and guidance it would be most helpful if you
could explain each piece in the code so that I understand what I'm doing.

Thanks so much.

Ellen

 

[excelent]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.ColorIndex = 1 Then Target.Offset(2, 5).Select
If Target.Interior.ColorIndex = 2 Then Target.Offset(4, 5).Select
If Target.Interior.ColorIndex = 3 Then Target.Offset(6, 5).Select
End Sub

"Ellen G" skrev:

 

[Pete_UK]

You can do it with a hyperlink formula - no need for code. Suppose
your blue section begins in A6 (i.e. the cell you want to jump to),
then put this formula in the blue key cell:

=HYPERLINK("#"&"A6","")

If your red section begins at cell A46, then use this formula in the
red key cell:

=HYPERLINK("#"&"A46","")

You can see how the formula varies - the cell reference is the cell
you want to jump to, so put similar formulae in the other coloured
cells of your key.

If you click on the red key cell, your cursor will jump to A46. Click
on another coloured key cell, and the cursor will jump to where that
cell points to.

Hope this helps.

Pete

 

[Ellen G]

I'm not sure this will work since I have other cells in the spreadsheet with
the same color. When those are accessed, I don't want the cursor to jump. So,
could you provide me with a formula requiring text to be typed in the cell
(i.e. "go1", "go2", etc.)? Thanks so much.

Ellen

 

[Gord Dibben]

Revised...........

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A1:C1"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Interior.ColorIndex = 1 Then Target.Offset(2, 5).Select
If Target.Interior.ColorIndex = 2 Then Target.Offset(4, 5).Select
If Target.Interior.ColorIndex = 3 Then Target.Offset(6, 5).Select
End If
End Sub


Gord Dibben MS Excel MVP

 

[Ellen G]

Perfect! That worked beautifully. Thanks so much.

Ellen

 

[Pete_UK]

You're welcome, Ellen - thanks for feeding back.

Pete