Unqualified ranges refer to the active sheet when the code is in a general
module, but when you have it behind a worksheet, those unqualified ranges refer
to the sheet that own the code.

so Range("c38") still refered to the sheet that held the code--and you can't
select a range on a sheet that isn't selected.

Try it this way:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$J$13" Then
With Worksheets("SGE")
.Activate
.Range("C38").Select
End With
End If
End Sub



Tony wrote:

Hi Ste Mac

Thank you for your help but I need a bit more of your assistance if possible.

Following your suggestion I have created the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$J$13" Then
Worksheets("SGE").Activate
' Cells(38, 3).Select
Range("C38").Select
End If
End Sub

After changing selection of my list selection moves to cell C38 but I am
getting the following error message:

Run-time error '1004'
Select method of Range class failed

What is wrong and how I can fix it.

Thank you for your help.

Regards,

Tony

"ste mac" wrote:

Hi Tony, try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$3" Then 'B3 changed to your list output cell
Range("D1").Select ' D1 changed to the cell where you want to go
End If
End Sub


Just paste this code in the worksheet your list is in......

hope this helps

seeya ste


--

Dave Peterson