I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump I
would like that whole column to be grey, enter my data then return to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.

Bryan
I assumed that the names in sheet 1 are in Column A starting in A2. I
assumed that sheet 2 is named "Two". I assumed that the active cell is in
the row, in sheet 1, that has the name, in Column A, that you want to jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1 starting
with cell A1. I gather that you want to place a button in sheet 1 to make
this happen. Assign the following macro to that button. Note that the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End Sub
wrote in message
...
I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump I
would like that whole column to be grey, enter my data then return to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.

Hi Bryan,
What you can do is to unprotect the cells where you want the information to
be entered and then protect the sheet. Although you can highlight the cells
where the information has to be entered. So if somebody wants to enter
information outside your parameters a message error will show up

" wrote:

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump I
would like that whole column to be grey, enter my data then return to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.

On 17 Nov, 18:21, "Otto Moehrbach"
wrote:
Bryan
* * I assumed that the names in sheet 1 are in Column A starting in A2. *I
assumed that sheet 2 is named "Two". *I assumed that the active cell is in
the row, in sheet 1, that has the name, in Column A, that you want to jump
to in sheet Two. *I assumed that the names are in sheet 2 in Row 1 starting
with cell A1. *I gather that you want to place a button in sheet 1 to make
this happen. *Assign the following macro to that button. *Note that the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of sheet
"Two". *HTH *Otto
Sub JumpShade()
* * Dim TheName As String
* * Dim Sht2Row1 As Range
* * Application.ScreenUpdating = False
* * TheName = Cells(ActiveCell.Row, 1).Value
* * Sheets("Two").Select
* * Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
* * Cells.Interior.ColorIndex = xlNone
* * Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
* * * * .EntireColumn.Interior.ColorIndex = 15
* * Application.ScreenUpdating = True
End wrote in message

...



I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump I
would like that whole column to be grey, enter my data then return to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.

Here is the revised macro. The location of the names in the first sheet is
immaterial except that the names are in Column A (doesn't matter what rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End Sub
wrote in message
...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:
Bryan
I assumed that the names in sheet 1 are in Column A starting in A2. I
assumed that sheet 2 is named "Two". I assumed that the active cell is in
the row, in sheet 1, that has the name, in Column A, that you want to jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1 starting
with cell A1. I gather that you want to place a button in sheet 1 to make
this happen. Assign the following macro to that button. Note that the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...



I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump I
would like that whole column to be grey, enter my data then return to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.

On 17 Nov, 21:22, "Otto Moehrbach"
wrote:
Here is the revised macro. *The location of the names in the first sheet is
immaterial except that the names are in Column A (doesn't matter what rows).
HTH *Otto
Sub JumpShade()
* * Dim TheName As String
* * Dim ShtANames As Range
* * Application.ScreenUpdating = False
* * TheName = Cells(ActiveCell.Row, 1).Value
* * Sheets("A").Select
* * Set ShtANames = Range("E6:IT6")
* * Cells.Interior.ColorIndex = xlNone
* * ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
* * * * .EntireColumn.Interior.ColorIndex = 15
* * Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:





Bryan
I assumed that the names in sheet 1 are in Column A starting in A2. I
assumed that sheet 2 is named "Two". I assumed that the active cell is in
the row, in sheet 1, that has the name, in Column A, that you want to jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1 starting
with cell A1. I gather that you want to place a button in sheet 1 to make
this happen. Assign the following macro to that button. Note that the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

....

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump I
would like that whole column to be grey, enter my data then return to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.- Hide quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan

On 17 Nov, 21:50, wrote:
On 17 Nov, 21:22, "Otto Moehrbach"
wrote:





Here is the revised macro. *The location of the names in the first sheet is
immaterial except that the names are in Column A (doesn't matter what rows).
HTH *Otto
Sub JumpShade()
* * Dim TheName As String
* * Dim ShtANames As Range
* * Application.ScreenUpdating = False
* * TheName = Cells(ActiveCell.Row, 1).Value
* * Sheets("A").Select
* * Set ShtANames = Range("E6:IT6")
* * Cells.Interior.ColorIndex = xlNone
* * ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
* * * * .EntireColumn.Interior.ColorIndex = 15
* * Application.ScreenUpdating = True
End wrote in message

....
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in A2. I
assumed that sheet 2 is named "Two". I assumed that the active cell is in
the row, in sheet 1, that has the name, in Column A, that you want to jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1 starting
with cell A1. I gather that you want to place a button in sheet 1 to make
this happen. Assign the following macro to that button. Note that the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

....

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump I
would like that whole column to be grey, enter my data then return to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.- Hide quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan

Bryan
What did you do with the macro? Exactly. What did you do to run the
macro? What is your macro security set at? What version of Excel are you
using? Otto
wrote in message
...
On 17 Nov, 21:50, wrote:
On 17 Nov, 21:22, "Otto Moehrbach"
wrote:





Here is the revised macro. The location of the names in the first sheet
is
immaterial except that the names are in Column A (doesn't matter what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in A2. I
assumed that sheet 2 is named "Two". I assumed that the active cell is
in
the row, in sheet 1, that has the name, in Column A, that you want to
jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1
starting
with cell A1. I gather that you want to place a button in sheet 1 to
make
this happen. Assign the following macro to that button. Note that the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump
I
would like that whole column to be grey, enter my data then return
to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to
enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.- Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan

On 18 Nov, 16:59, "Otto Moehrbach"
wrote:
Bryan
* * What did you do with the macro? *Exactly. *What did you do to run the
macro? *What is your macro security set at? *What version of Excel are you
using? wrote in message

...
On 17 Nov, 21:50, wrote:





On 17 Nov, 21:22, "Otto Moehrbach"
wrote:

Here is the revised macro. The location of the names in the first sheet
is
immaterial except that the names are in Column A (doesn't matter what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

....
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in A2. I
assumed that sheet 2 is named "Two". I assumed that the active cell is
in
the row, in sheet 1, that has the name, in Column A, that you want to
jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1
starting
with cell A1. I gather that you want to place a button in sheet 1 to
make
this happen. Assign the following macro to that button. Note that the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the same
names with other data. If on sheet1 I click on jump, it will jump to
that name on sheet2. What I want to try and do is this. When I jump
I
would like that whole column to be grey, enter my data then return
to
sheet1. Then if I jump on another name then that column to be grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to
enter
data in the grey cell without maybe using another cell by mistake..

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.- Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan- Hide quoted text -

- Show quoted text -

Otto, I've tried putting the macro in the thisworkbook, I've also put
it on each sheet, 1 at a time naturally. I've saved and then reopened.
I've clicked jump and it's gone to the 'A' sheet to the correct name,
but the cells remain white. I've been up to macro clicked and run, I
get an error message Run-time error '91' object variable or with block
variable not set.
This happens no matter what sheet I put the macro in. I'm using 2000.
The security is on the low setting with no other protction engaged.

Bryan.

Bryan
I assume when you say you put the macro in ThisWorkbook that you mean
the ThisWorkbook module. I assume you are also putting it in the sheet
modules. Don't do that. When you are in the VBE (Visual Basic Editor, the
place where the code resides) and the name of your file is shown at the top
of the VBE, click on Insert - Module. That is a regular module. Paste the
macro into that module. If you are using a button to run the macro,
reassign that macro to the button. HTH Otto
wrote in message
...
On 18 Nov, 16:59, "Otto Moehrbach"
wrote:
Bryan
What did you do with the macro? Exactly. What did you do to run the
macro? What is your macro security set at? What version of Excel are you
using? wrote in message

...
On 17 Nov, 21:50, wrote:





On 17 Nov, 21:22, "Otto Moehrbach"
wrote:

Here is the revised macro. The location of the names in the first
sheet
is
immaterial except that the names are in Column A (doesn't matter what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in A2.
I
assumed that sheet 2 is named "Two". I assumed that the active cell
is
in
the row, in sheet 1, that has the name, in Column A, that you want
to
jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1
starting
with cell A1. I gather that you want to place a button in sheet 1 to
make
this happen. Assign the following macro to that button. Note that
the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the
same
names with other data. If on sheet1 I click on jump, it will jump
to
that name on sheet2. What I want to try and do is this. When I
jump
I
would like that whole column to be grey, enter my data then return
to
sheet1. Then if I jump on another name then that column to be
grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to
enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.- Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan- Hide quoted text -

- Show quoted text -

Otto, I've tried putting the macro in the thisworkbook, I've also put
it on each sheet, 1 at a time naturally. I've saved and then reopened.
I've clicked jump and it's gone to the 'A' sheet to the correct name,
but the cells remain white. I've been up to macro clicked and run, I
get an error message Run-time error '91' object variable or with block
variable not set.
This happens no matter what sheet I put the macro in. I'm using 2000.
The security is on the low setting with no other protction engaged.

Bryan.

On 18 Nov, 20:05, "Otto Moehrbach"
wrote:
Bryan
* * I assume when you say you put the macro in ThisWorkbook that you mean
the ThisWorkbook module. *I assume you are also putting it in the sheet
modules. *Don't do that. *When you are in the VBE (Visual Basic Editor, the
place where the code resides) and the name of your file is shown at the top
of the VBE, click on Insert - Module. *That is a regular module. *Paste the
macro into that module. *If you are using a button to run the macro,
reassign that macro to the button. *HTH wrote in message

...
On 18 Nov, 16:59, "Otto Moehrbach"
wrote:





Bryan
What did you do with the macro? Exactly. What did you do to run the
macro? What is your macro security set at? What version of Excel are you
using? wrote in message

....
On 17 Nov, 21:50, wrote:

On 17 Nov, 21:22, "Otto Moehrbach"
wrote:

Here is the revised macro. The location of the names in the first
sheet
is
immaterial except that the names are in Column A (doesn't matter what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in A2.
I
assumed that sheet 2 is named "Two". I assumed that the active cell
is
in
the row, in sheet 1, that has the name, in Column A, that you want
to
jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1
starting
with cell A1. I gather that you want to place a button in sheet 1 to
make
this happen. Assign the following macro to that button. Note that
the
names in Column A of sheet1 must match EXACTLY the names in Row 1 of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the
same
names with other data. If on sheet1 I click on jump, it will jump
to
that name on sheet2. What I want to try and do is this. When I
jump
I
would like that whole column to be grey, enter my data then return
to
sheet1. Then if I jump on another name then that column to be
grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to
enter
data in the grey cell without maybe using another cell by mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.- Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan- Hide quoted text -

- Show quoted text -

Otto, I've tried putting the macro in the thisworkbook, I've also put
it on each sheet, 1 at a time naturally. I've saved and then reopened.
I've clicked jump and it's gone to the 'A' sheet to the correct name,
but the cells remain white. I've been up to macro clicked and run, I
get an error message Run-time error '91' object variable or with block
variable not set.
This happens no matter what sheet I put the macro in. I'm using 2000.
The security is on *the low setting with no other protction engaged.

Bryan.- Hide quoted text -

- Show quoted text -

Hi again Otto, thanks for being patient with me.
I have done exactly as you have mentioned. I have assigned a button to
that macro. When clicked it comes up with an error still ' 91' object
variable or with block variable not set and an option to debug. When I
click debug it goes into the VBE and shows the macro with this
highlighted in yellow

ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15

Bryan.

Bryan
That indicates that it couldn't find the name. Remember that it looks
at Column A of the row that holds the active cell. The value in that cell
is set to the variable TheName. Then it looks at E6:IT6 of the "A" sheet
and looks for that name. It couldn't find the name and that produced the
error. I would advise you to copy/paste all the names from the first sheet
to the "A" sheet to ensure that they are the same. Otto
wrote in message
...
On 18 Nov, 20:05, "Otto Moehrbach"
wrote:
Bryan
I assume when you say you put the macro in ThisWorkbook that you mean
the ThisWorkbook module. I assume you are also putting it in the sheet
modules. Don't do that. When you are in the VBE (Visual Basic Editor, the
place where the code resides) and the name of your file is shown at the
top
of the VBE, click on Insert - Module. That is a regular module. Paste the
macro into that module. If you are using a button to run the macro,
reassign that macro to the button. HTH
wrote in message

...
On 18 Nov, 16:59, "Otto Moehrbach"
wrote:





Bryan
What did you do with the macro? Exactly. What did you do to run the
macro? What is your macro security set at? What version of Excel are you
using? wrote in message

...
On 17 Nov, 21:50, wrote:

On 17 Nov, 21:22, "Otto Moehrbach"
wrote:

Here is the revised macro. The location of the names in the first
sheet
is
immaterial except that the names are in Column A (doesn't matter
what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in
A2.
I
assumed that sheet 2 is named "Two". I assumed that the active
cell
is
in
the row, in sheet 1, that has the name, in Column A, that you want
to
jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1
starting
with cell A1. I gather that you want to place a button in sheet 1
to
make
this happen. Assign the following macro to that button. Note that
the
names in Column A of sheet1 must match EXACTLY the names in Row 1
of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the
same
names with other data. If on sheet1 I click on jump, it will
jump
to
that name on sheet2. What I want to try and do is this. When I
jump
I
would like that whole column to be grey, enter my data then
return
to
sheet1. Then if I jump on another name then that column to be
grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to
enter
data in the grey cell without maybe using another cell by
mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.- Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan- Hide quoted text -

- Show quoted text -

Otto, I've tried putting the macro in the thisworkbook, I've also put
it on each sheet, 1 at a time naturally. I've saved and then reopened.
I've clicked jump and it's gone to the 'A' sheet to the correct name,
but the cells remain white. I've been up to macro clicked and run, I
get an error message Run-time error '91' object variable or with block
variable not set.
This happens no matter what sheet I put the macro in. I'm using 2000.
The security is on the low setting with no other protction engaged.

Bryan.- Hide quoted text -

- Show quoted text -

Hi again Otto, thanks for being patient with me.
I have done exactly as you have mentioned. I have assigned a button to
that macro. When clicked it comes up with an error still ' 91' object
variable or with block variable not set and an option to debug. When I
click debug it goes into the VBE and shows the macro with this
highlighted in yellow

ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15

Bryan.

On 18 Nov, 21:53, "Otto Moehrbach"
wrote:
Bryan
* * That indicates that it couldn't find the name. *Remember that it looks
at Column A of the row that holds the active cell. *The value in that cell
is set to the variable TheName. *Then it looks at E6:IT6 of the "A" sheet
and looks for that name. *It couldn't find the name and that produced the
error. *I would advise you to copy/paste all the names from the first sheet
to the "A" sheet to ensure that they are the same. wrote in message

...
On 18 Nov, 20:05, "Otto Moehrbach"
wrote:





Bryan
I assume when you say you put the macro in ThisWorkbook that you mean
the ThisWorkbook module. I assume you are also putting it in the sheet
modules. Don't do that. When you are in the VBE (Visual Basic Editor, the
place where the code resides) and the name of your file is shown at the
top
of the VBE, click on Insert - Module. That is a regular module. Paste the
macro into that module. If you are using a button to run the macro,
reassign that macro to the button. HTH
wrote in message

...
On 18 Nov, 16:59, "Otto Moehrbach"
wrote:

Bryan
What did you do with the macro? Exactly. What did you do to run the
macro? What is your macro security set at? What version of Excel are you
using? wrote in message

....
On 17 Nov, 21:50, wrote:

On 17 Nov, 21:22, "Otto Moehrbach"
wrote:

Here is the revised macro. The location of the names in the first
sheet
is
immaterial except that the names are in Column A (doesn't matter
what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in
A2.
I
assumed that sheet 2 is named "Two". I assumed that the active
cell
is
in
the row, in sheet 1, that has the name, in Column A, that you want
to
jump
to in sheet Two. I assumed that the names are in sheet 2 in Row 1
starting
with cell A1. I gather that you want to place a button in sheet 1
to
make
this happen. Assign the following macro to that button. Note that
the
names in Column A of sheet1 must match EXACTLY the names in Row 1
of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the
same
names with other data. If on sheet1 I click on jump, it will
jump
to
that name on sheet2. What I want to try and do is this. When I
jump
I
would like that whole column to be grey, enter my data then
return
to
sheet1. Then if I jump on another name then that column to be
grey.
Hopefully when I jumped the first time and it greyed out, when I
returned to sheet1 it went back to white. It would be easier to
enter
data in the grey cell without maybe using another cell by
mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down to
A255 on sheet2 which is called A the names go from E6 to IT6.- Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan- Hide quoted text -

- Show quoted text -

Otto, I've tried putting the macro in the thisworkbook, I've also put
it on each sheet, 1 at a time naturally. I've saved and then reopened.
I've clicked jump and it's gone to the 'A' sheet to the correct name,
but the cells remain white. I've been up to macro clicked and run, I
get an error message Run-time error '91' object variable or with block
variable not set.
This happens no matter what sheet I put the macro in. I'm using 2000.
The security is on the low setting with no other protction engaged.

Bryan.- Hide quoted text -

- Show quoted text -

Hi again Otto, thanks for being patient with me.
I have done exactly as you have mentioned. I have assigned a button to
that macro. When clicked it comes up with an error still ' 91' object
variable or with block variable not set and an option to debug. When I
click debug it goes into the VBE and shows the macro with this
highlighted in yellow

ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
* * * * .EntireColumn.Interior.ColorIndex = 15

Bryan.- Hide quoted text -

- Show quoted text -

Otto, on sheet 2 (home) I have the names. On sheet 3 (A) I have =Home!
A6 which is copied all the way across so that all the names match.
On the jump hyperlink it's thus =HYPERLINK("#A!R6C"&MATCH(A12,A!$D
$6:$IS$6,0)+3,"jump")
This works fine, it jumps to the correct name on sheet A. The column
just doesn't shade.

Bryan
Put ONE of these 2 macros in the sheet module of the "A" sheet. Then do
what you do and see if it works for you. If not, remove that macro and
replace it with the other macro and try that. I didn't know that you were
jumping to the "A" sheet with a hyperlink. HTH Otto
Private Sub Worksheet_Activate()
Cells.Interior.ColorIndex = xlNone
ActiveCell.EntireColumn.Interior.ColorIndex = 15
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
ActiveCell.EntireColumn.Interior.ColorIndex = 15
End Sub
wrote in message
...
On 18 Nov, 21:53, "Otto Moehrbach"
wrote:
Bryan
That indicates that it couldn't find the name. Remember that it looks
at Column A of the row that holds the active cell. The value in that cell
is set to the variable TheName. Then it looks at E6:IT6 of the "A" sheet
and looks for that name. It couldn't find the name and that produced the
error. I would advise you to copy/paste all the names from the first sheet
to the "A" sheet to ensure that they are the same.
wrote in message

...
On 18 Nov, 20:05, "Otto Moehrbach"
wrote:





Bryan
I assume when you say you put the macro in ThisWorkbook that you mean
the ThisWorkbook module. I assume you are also putting it in the sheet
modules. Don't do that. When you are in the VBE (Visual Basic Editor,
the
place where the code resides) and the name of your file is shown at the
top
of the VBE, click on Insert - Module. That is a regular module. Paste
the
macro into that module. If you are using a button to run the macro,
reassign that macro to the button. HTH
wrote in message

...
On 18 Nov, 16:59, "Otto Moehrbach"
wrote:

Bryan
What did you do with the macro? Exactly. What did you do to run the
macro? What is your macro security set at? What version of Excel are
you
using? wrote in message

...
On 17 Nov, 21:50, wrote:

On 17 Nov, 21:22, "Otto Moehrbach"
wrote:

Here is the revised macro. The location of the names in the first
sheet
is
immaterial except that the names are in Column A (doesn't matter
what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in
A2.
I
assumed that sheet 2 is named "Two". I assumed that the active
cell
is
in
the row, in sheet 1, that has the name, in Column A, that you
want
to
jump
to in sheet Two. I assumed that the names are in sheet 2 in Row
1
starting
with cell A1. I gather that you want to place a button in sheet
1
to
make
this happen. Assign the following macro to that button. Note
that
the
names in Column A of sheet1 must match EXACTLY the names in Row
1
of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1,
Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the
same
names with other data. If on sheet1 I click on jump, it will
jump
to
that name on sheet2. What I want to try and do is this. When I
jump
I
would like that whole column to be grey, enter my data then
return
to
sheet1. Then if I jump on another name then that column to be
grey.
Hopefully when I jumped the first time and it greyed out, when
I
returned to sheet1 it went back to white. It would be easier
to
enter
data in the grey cell without maybe using another cell by
mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down
to
A255 on sheet2 which is called A the names go from E6 to IT6.-
Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan- Hide quoted text -

- Show quoted text -

Otto, I've tried putting the macro in the thisworkbook, I've also put
it on each sheet, 1 at a time naturally. I've saved and then reopened.
I've clicked jump and it's gone to the 'A' sheet to the correct name,
but the cells remain white. I've been up to macro clicked and run, I
get an error message Run-time error '91' object variable or with block
variable not set.
This happens no matter what sheet I put the macro in. I'm using 2000.
The security is on the low setting with no other protction engaged.

Bryan.- Hide quoted text -

- Show quoted text -

Hi again Otto, thanks for being patient with me.
I have done exactly as you have mentioned. I have assigned a button to
that macro. When clicked it comes up with an error still ' 91' object
variable or with block variable not set and an option to debug. When I
click debug it goes into the VBE and shows the macro with this
highlighted in yellow

ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15

Bryan.- Hide quoted text -

- Show quoted text -

Otto, on sheet 2 (home) I have the names. On sheet 3 (A) I have =Home!
A6 which is copied all the way across so that all the names match.
On the jump hyperlink it's thus =HYPERLINK("#A!R6C"&MATCH(A12,A!$D
$6:$IS$6,0)+3,"jump")
This works fine, it jumps to the correct name on sheet A. The column
just doesn't shade.

On 19 Nov, 17:15, "Otto Moehrbach"
wrote:
Bryan
* * Put ONE of these 2 macros in the sheet module of the "A" sheet. *Then do
what you do and see if it works for you. *If not, remove that macro and
replace it with the other macro and try that. *I didn't know that you were
jumping to the "A" sheet with a hyperlink. *HTH *Otto
Private Sub Worksheet_Activate()
* * Cells.Interior.ColorIndex = xlNone
* * ActiveCell.EntireColumn.Interior.ColorIndex = 15
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
* * Cells.Interior.ColorIndex = xlNone
* * ActiveCell.EntireColumn.Interior.ColorIndex = 15
End wrote in message

...
On 18 Nov, 21:53, "Otto Moehrbach"
wrote:





Bryan
That indicates that it couldn't find the name. Remember that it looks
at Column A of the row that holds the active cell. The value in that cell
is set to the variable TheName. Then it looks at E6:IT6 of the "A" sheet
and looks for that name. It couldn't find the name and that produced the
error. I would advise you to copy/paste all the names from the first sheet
to the "A" sheet to ensure that they are the same.
wrote in message

....
On 18 Nov, 20:05, "Otto Moehrbach"
wrote:

Bryan
I assume when you say you put the macro in ThisWorkbook that you mean
the ThisWorkbook module. I assume you are also putting it in the sheet
modules. Don't do that. When you are in the VBE (Visual Basic Editor,
the
place where the code resides) and the name of your file is shown at the
top
of the VBE, click on Insert - Module. That is a regular module. Paste
the
macro into that module. If you are using a button to run the macro,
reassign that macro to the button. HTH
wrote in message

....
On 18 Nov, 16:59, "Otto Moehrbach"
wrote:

Bryan
What did you do with the macro? Exactly. What did you do to run the
macro? What is your macro security set at? What version of Excel are
you
using? wrote in message

...
On 17 Nov, 21:50, wrote:

On 17 Nov, 21:22, "Otto Moehrbach"
wrote:

Here is the revised macro. The location of the names in the first
sheet
is
immaterial except that the names are in Column A (doesn't matter
what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"
wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting in
A2.
I
assumed that sheet 2 is named "Two". I assumed that the active
cell
is
in
the row, in sheet 1, that has the name, in Column A, that you
want
to
jump
to in sheet Two. I assumed that the names are in sheet 2 in Row
1
starting
with cell A1. I gather that you want to place a button in sheet
1
to
make
this happen. Assign the following macro to that button. Note
that
the
names in Column A of sheet1 must match EXACTLY the names in Row
1
of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1,
Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has the
same
names with other data. If on sheet1 I click on jump, it will
jump
to
that name on sheet2. What I want to try and do is this. When I
jump
I
would like that whole column to be grey, enter my data then
return
to
sheet1. Then if I jump on another name then that column to be
grey.
Hopefully when I jumped the first time and it greyed out, when
I
returned to sheet1 it went back to white. It would be easier
to
enter
data in the grey cell without maybe using another cell by
mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down
to
A255 on sheet2 which is called A the names go from E6 to IT6.-
Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each time
the same result.

Bryan- Hide quoted text -

- Show quoted text -

Otto, I've tried putting the macro in the thisworkbook, I've also put
it on each sheet, 1 at a time naturally. I've saved and then reopened..
I've clicked jump and it's gone to the 'A' sheet to the correct name,
but the cells remain white. I've been up to macro clicked and run, I
get an error message Run-time error '91' object variable or with block
variable not set.
This happens no matter what sheet I put the macro in. I'm using 2000.
The security is on the low setting with no other protction engaged.

Bryan.- Hide quoted text -

- Show quoted text -

Hi again Otto, thanks for being patient with me.
I have done exactly as you have mentioned. I have assigned a button to
that macro. When clicked it comes up with an error still ' 91' object
variable or with block variable not set and an option to debug. When I
click debug it goes into the VBE and shows the macro with this
highlighted in yellow

ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15

Bryan.- Hide quoted text -

- Show quoted text -

Otto, on sheet 2 (home) I have the names. On sheet 3 (A) I have =Home!
A6 which is copied all the way across so that all the names match.
On the jump hyperlink it's thus =HYPERLINK("#A!R6C"&MATCH(A12,A!$D
$6:$IS$6,0)+3,"jump")
This works fine, it jumps to the correct name on sheet A. The column
just doesn't shade.- Hide quoted text -

- Show quoted text -

Great Otto, the second macro works a treat. Sorry about the confusion
of the hyperlink

Good! Thanks for the feedback. Otto
wrote in message
...
On 19 Nov, 17:15, "Otto Moehrbach"
wrote:
Bryan
Put ONE of these 2 macros in the sheet module of the "A" sheet. Then do
what you do and see if it works for you. If not, remove that macro and
replace it with the other macro and try that. I didn't know that you were
jumping to the "A" sheet with a hyperlink. HTH Otto
Private Sub Worksheet_Activate()
Cells.Interior.ColorIndex = xlNone
ActiveCell.EntireColumn.Interior.ColorIndex = 15
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
ActiveCell.EntireColumn.Interior.ColorIndex = 15
End wrote in message

...
On 18 Nov, 21:53, "Otto Moehrbach"
wrote:





Bryan
That indicates that it couldn't find the name. Remember that it looks
at Column A of the row that holds the active cell. The value in that
cell
is set to the variable TheName. Then it looks at E6:IT6 of the "A" sheet
and looks for that name. It couldn't find the name and that produced the
error. I would advise you to copy/paste all the names from the first
sheet
to the "A" sheet to ensure that they are the same.
wrote in message

...
On 18 Nov, 20:05, "Otto Moehrbach"
wrote:

Bryan
I assume when you say you put the macro in ThisWorkbook that you mean
the ThisWorkbook module. I assume you are also putting it in the sheet
modules. Don't do that. When you are in the VBE (Visual Basic Editor,
the
place where the code resides) and the name of your file is shown at
the
top
of the VBE, click on Insert - Module. That is a regular module. Paste
the
macro into that module. If you are using a button to run the macro,
reassign that macro to the button. HTH
wrote in message

...
On 18 Nov, 16:59, "Otto Moehrbach"
wrote:

Bryan
What did you do with the macro? Exactly. What did you do to run the
macro? What is your macro security set at? What version of Excel are
you
using? wrote in message

...
On 17 Nov, 21:50, wrote:

On 17 Nov, 21:22, "Otto Moehrbach"
wrote:

Here is the revised macro. The location of the names in the
first
sheet
is
immaterial except that the names are in Column A (doesn't matter
what
rows).
HTH Otto
Sub JumpShade()
Dim TheName As String
Dim ShtANames As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("A").Select
Set ShtANames = Range("E6:IT6")
Cells.Interior.ColorIndex = xlNone
ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...
On 17 Nov, 18:21, "Otto Moehrbach"

wrote:

Bryan
I assumed that the names in sheet 1 are in Column A starting
in
A2.
I
assumed that sheet 2 is named "Two". I assumed that the active
cell
is
in
the row, in sheet 1, that has the name, in Column A, that you
want
to
jump
to in sheet Two. I assumed that the names are in sheet 2 in
Row
1
starting
with cell A1. I gather that you want to place a button in
sheet
1
to
make
this happen. Assign the following macro to that button. Note
that
the
names in Column A of sheet1 must match EXACTLY the names in
Row
1
of
sheet
"Two". HTH Otto
Sub JumpShade()
Dim TheName As String
Dim Sht2Row1 As Range
Application.ScreenUpdating = False
TheName = Cells(ActiveCell.Row, 1).Value
Sheets("Two").Select
Set Sht2Row1 = Range("A1", Cells(1,
Columns.Count).End(xlToLeft))
Cells.Interior.ColorIndex = xlNone
Sht2Row1.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15
Application.ScreenUpdating = True
End wrote in message

...

I have a 3 sheet workbook. Sheet1 has names etc, sheet2 has
the
same
names with other data. If on sheet1 I click on jump, it will
jump
to
that name on sheet2. What I want to try and do is this. When
I
jump
I
would like that whole column to be grey, enter my data then
return
to
sheet1. Then if I jump on another name then that column to
be
grey.
Hopefully when I jumped the first time and it greyed out,
when
I
returned to sheet1 it went back to white. It would be easier
to
enter
data in the grey cell without maybe using another cell by
mistake.

Thanks for any help I may get.- Hide quoted text -

- Show quoted text -

Ummm not exactly. I haven't explained it well at now I read your
reply. On sheet1 which is called Home the names are from A6 down
to
A255 on sheet2 which is called A the names go from E6 to IT6.-
Hide
quoted text -

- Show quoted text -

Thanks Ootto, I will try it when I get home. Thanks again.

Bryan- Hide quoted text -

- Show quoted text -

Otto, thanks for the assistance. I have added the macro but nothing
happens at all. No errors messages, nothing happens. I've tried
putting the macro on each page in case I was doing it wrong, each
time
the same result.

Bryan- Hide quoted text -

- Show quoted text -

Otto, I've tried putting the macro in the thisworkbook, I've also put
it on each sheet, 1 at a time naturally. I've saved and then reopened.
I've clicked jump and it's gone to the 'A' sheet to the correct name,
but the cells remain white. I've been up to macro clicked and run, I
get an error message Run-time error '91' object variable or with block
variable not set.
This happens no matter what sheet I put the macro in. I'm using 2000.
The security is on the low setting with no other protction engaged.

Bryan.- Hide quoted text -

- Show quoted text -

Hi again Otto, thanks for being patient with me.
I have done exactly as you have mentioned. I have assigned a button to
that macro. When clicked it comes up with an error still ' 91' object
variable or with block variable not set and an option to debug. When I
click debug it goes into the VBE and shows the macro with this
highlighted in yellow

ShtANames.Find(What:=TheName, Lookat:=xlWhole) _
.EntireColumn.Interior.ColorIndex = 15

Bryan.- Hide quoted text -

- Show quoted text -

Otto, on sheet 2 (home) I have the names. On sheet 3 (A) I have =Home!
A6 which is copied all the way across so that all the names match.
On the jump hyperlink it's thus =HYPERLINK("#A!R6C"&MATCH(A12,A!$D
$6:$IS$6,0)+3,"jump")
This works fine, it jumps to the correct name on sheet A. The column
just doesn't shade.- Hide quoted text -

- Show quoted text -

Great Otto, the second macro works a treat. Sorry about the confusion
of the hyperlink