I currently use this workaround which works:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Target.Offset(0, 2).Select
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("E5:E35")) Is Nothing Then Exit Sub
If Target.Value 1 Then
'---continue sub

I would like the input range in Column C to be limited to "C5:E35"
e.g. If Intersect(Target, Range("C5:C35")) Then Target.Offset(0, 2).Select

This gets me to desired cell in Column E, but then things bomb 'Runtime
error 91' as soon as I input anything in "E5:E35"

How to fix?

--
David

David,

You're not looking are you? The answer is already in the code, use

If Intersect(Target, Range("C5:C35")) Is Nothing Then Target.Offset(0,
2).Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"David" wrote in message
...
I currently use this workaround which works:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Target.Offset(0, 2).Select
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("E5:E35")) Is Nothing Then Exit Sub
If Target.Value 1 Then
'---continue sub

I would like the input range in Column C to be limited to "C5:E35"
e.g. If Intersect(Target, Range("C5:C35")) Then Target.Offset(0, 2).Select

This gets me to desired cell in Column E, but then things bomb 'Runtime
error 91' as soon as I input anything in "E5:E35"

How to fix?

--
David

Bob Phillips wrote

You're not looking are you? The answer is already in the code, use

If Intersect(Target, Range("C5:C35")) Is Nothing Then Target.Offset(0,
2).Select


Well, if it were that obvious, I would have used it. Problem is I only want
that to happen (Target.Offset(0,2).Select) if I enter something in C5:C35.
Your offering moves 2 cols over no matter where I enter something.

--
David

David wrote

I currently use this workaround which works:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Target.Offset(0, 2).Select
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("E5:E35")) Is Nothing Then Exit Sub
If Target.Value 1 Then
'---continue sub

I would like the input range in Column C to be limited to "C5:E35"
e.g. If Intersect(Target, Range("C5:C35")) Then Target.Offset(0,
2).Select

This gets me to desired cell in Column E, but then things bomb
'Runtime error 91' as soon as I input anything in "E5:E35"

How to fix?


This works the way I want:
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then Target.Offset(0,
2).Select

--
David

No you wouldn't because you omitted to test for Is Nothing that is why you
got an error. If you couldn't see that it is unlikely you can solve it.

And it is impossible to be in column C and in E, so better coding would
handle than more efficiently. As it is, rather than re-cut all the code, I
tried to fix it in your style, but I made a small mistake in the code I
posted which I am sure you are now going to spot and solve for yourself.

RP

"David" wrote in message
...
Bob Phillips wrote

You're not looking are you? The answer is already in the code, use

If Intersect(Target, Range("C5:C35")) Is Nothing Then Target.Offset(0,
2).Select


Well, if it were that obvious, I would have used it. Problem is I only
want
that to happen (Target.Offset(0,2).Select) if I enter something in C5:C35.
Your offering moves 2 cols over no matter where I enter something.

--
David