GOTO a specific cell, based on another cell's data

JMB17 

hello,

i have a question in column B where i use data validation rules to get the user answers YES or NO. When the user answers YES then he needs to go to cell C on the same row, if he answers NO he has to go to cell D to provide more data. I tried the following code based on your example above but does not work. It always produces the error message below. What am I doing wrong? Is there a simpler way to do the same thing? THANK YOU

The code i used is :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim destCol As Range

On Error GoTo ErrHandler

If Intersect(Target.Cells(1), Me.Range("B:B")) Is Nothing Then
Exit Sub
End If

Select Case LCase(Target.Cells(1).Value)
Case vbNullString: Exit Sub
Case "YES": Set destCol = Me.Range("C:C")
Case "NO": Set destCol = Me.Range("D:D")

'// Add additional names here. Here, the names should
'// be in lowercase, but the user can key them in any case.

Case Else: Err.Raise Number:=vbObjectError + 520, Description:="NOT VALID ANSWER."

End Select

If Not destCol Is Nothing Then
Intersect(Target.Cells(1).EntireRow, destCol).Select
End If

ExitProc:
Exit Sub

ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc

End Sub