Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-jump to another cell
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-jump to another cell
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-jump to another cell
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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-jump to another cell
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-jump to another cell
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 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-jump to another cell
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 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-jump to another cell
You're welcome, Ellen - thanks for feeding back.
Pete On Nov 30, 7:42 pm, Ellen G 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 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
jump to 1st blank cell | Excel Worksheet Functions | |||
how do i enfoce a"auto-tab or jump" to next cell | Excel Discussion (Misc queries) | |||
Immediately jump to next cell? | Excel Discussion (Misc queries) | |||
HOW TO JUMP TO THE LAST CELL IN A ROW? | Excel Discussion (Misc queries) | |||
How do I double click a cell and jump to cell's referenced cell | Excel Discussion (Misc queries) |