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

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:

> 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
>
>

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




On Nov 30, 4:29 pm, Ellen G <Ell...@discussions.microsoft.com> wrote:
> 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

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

"excelent" wrote:

> 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:
>
> > 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
> >
> >

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

On Fri, 30 Nov 2007 09:03:02 -0800, Ellen G <(E-Mail Removed)>
wrote:

>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
>
>"excelent" wrote:
>
>> 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:
>>
>> > 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
>> >
>> >

Perfect! That worked beautifully. Thanks so much.

Ellen

"Pete_UK" wrote:

> 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
>
>
>
>
> On Nov 30, 4:29 pm, Ellen G <Ell...@discussions.microsoft.com> wrote:
> > 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
>
>

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

Pete

On Nov 30, 7:42 pm, Ellen G <Ell...@discussions.microsoft.com> wrote:
> Perfect! That worked beautifully. Thanks so much.
>
> Ellen
>
>
>
> "Pete_UK" wrote:
> > 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
>
> > On Nov 30, 4:29 pm, Ellen G <Ell...@discussions.microsoft.com> wrote:
> > > 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- Hide quoted text -
>
> - Show quoted text -