Move to a cell
 

If somebody types "yes" or "y" in cell C2 then I want the active cell
to be D2. If they put anything else in cell C2 i want the active cell
to move to C3 and then so down the page finishing at C101.(So if C3 is
"yes" then move to D3 etc)

What is the best way to achieve this please

Move to a cell
 

Hi Bob,
Does this Worksheetevent help?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Range("c2").EntireColumn) Is Nothing) Then
If LCase(Target.Value) = "yes" Or LCase(Target.Value) = "y" Then
Target.Offset(0, 1).Activate
End If
End If

End Sub

Mike, from Luxembourg

Move to a cell
 

Slight modification to deal with row range and possible copy/paste/delete of
a range of cells including some in column C:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MoveOver As Boolean

If Target.Cells.Count = 1 Then
With Target
MoveOver = (.Column = 3) And (.Row 1) And (.Row < 102)
MoveOver = MoveOver And (LCase(.Value) = "y" Or LCase(.Value) = "yes")
If MoveOver Then .Offset(0, 1).Activate Else .Offset(1, 0).Activate
End With
End If

End Sub


--
- K Dales


"Mike" wrote:

Hi Bob,
Does this Worksheetevent help?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Range("c2").EntireColumn) Is Nothing) Then
If LCase(Target.Value) = "yes" Or LCase(Target.Value) = "y" Then
Target.Offset(0, 1).Activate
End If
End If

End Sub

Mike, from Luxembourg

Move to a cell
 

Mike,
Perfect, a big thank you

Bob

Move to a cell
 

There is a conflict if a Row is subsequently deleted.
get R/T error '13'
type mismatch;
Can this be avoided? - How?

"Mike" wrote in message
...
Hi Bob,
Does this Worksheetevent help?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Range("c2").EntireColumn) Is Nothing) Then
If LCase(Target.Value) = "yes" Or LCase(Target.Value) = "y" Then
Target.Offset(0, 1).Activate
End If
End If

End Sub

Mike, from Luxembourg

Move to a cell
 

Jim May wrote:
There is a conflict if a Row is subsequently deleted.
get R/T error '13'
type mismatch;
Can this be avoided? - How?
Hello Jim,
you can check whether more cells are selected

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not (Intersect(Target, Range("c2").EntireColumn) Is Nothing) Then
If LCase(Target.Value) = "yes" Or LCase(Target.Value) = "y" Then
Target.Offset(0, 1).Activate
End If
End If
End If
End Sub