Offset to New Sheet
 

In "Sheet1", any cell may be selected (except in Columns A-E).
I need code that will will select the cell in "Sheet2", in the same Row as
in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is
selected, I need code that will select the cell Sheet2.B5.

Thanks for any help

Offset to New Sheet
 

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "F:IV"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Worksheets("Sheet2").Activate
ActiveSheet.Range(.Address).Offset(0, -4).Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kanga 85" wrote in message
...
In "Sheet1", any cell may be selected (except in Columns A-E).
I need code that will will select the cell in "Sheet2", in the same Row as
in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is
selected, I need code that will select the cell Sheet2.B5.

Thanks for any help

Offset to New Sheet
 

Thanks Bob,
I was not explict enuf. The bit of code I need is to be inserted into a
Macro I'm now using which, when called, does various things in Sheet1. I
now want to do an additional job in Sheet2 as part of the same Macro and I
just need to know how to select the appropriate cell.
Thanks.

"Bob Phillips" wrote:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "F:IV"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Worksheets("Sheet2").Activate
ActiveSheet.Range(.Address).Offset(0, -4).Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kanga 85" wrote in message
...
In "Sheet1", any cell may be selected (except in Columns A-E).
I need code that will will select the cell in "Sheet2", in the same Row as
in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is
selected, I need code that will select the cell Sheet2.B5.

Thanks for any help

Offset to New Sheet
 

If activeCell.Column = 5 then
application.Goto Worksheets("Sheet2").Range(ActiveCell.Address).Off set(0,-4)
End if

--
Regards,
Tom Ogilvy


"Kanga 85" wrote in message
...
In "Sheet1", any cell may be selected (except in Columns A-E).
I need code that will will select the cell in "Sheet2", in the same Row as
in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is
selected, I need code that will select the cell Sheet2.B5.

Thanks for any help

Offset to New Sheet
 

"Tom Ogilvy" wrote:

If activeCell.Column = 5 then
application.Goto Worksheets("Sheet2").Range(ActiveCell.Address).Off set(0,-4)
End if

--
Regards,
Tom Ogilvy


"Kanga 85" wrote in message
...
In "Sheet1", any cell may be selected (except in Columns A-E).
I need code that will will select the cell in "Sheet2", in the same Row as
in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is
selected, I need code that will select the cell Sheet2.B5.

Thanks for any help

Offset to New Sheet
 

Thanks Tom

"Tom Ogilvy" wrote:

If activeCell.Column = 5 then
application.Goto Worksheets("Sheet2").Range(ActiveCell.Address).Off set(0,-4)
End if

--
Regards,
Tom Ogilvy


"Kanga 85" wrote in message
...
In "Sheet1", any cell may be selected (except in Columns A-E).
I need code that will will select the cell in "Sheet2", in the same Row as
in "Sheet1', but offset 4 columns to the left. Thus if Sheet1.F5 is
selected, I need code that will select the cell Sheet2.B5.

Thanks for any help