Hello all,

Please help me. I'm very new to VBA and not sure how to make this work.
The problem is as follows:

There are 2 spreadsheets in my project. One is called Company Level
Gaps and the other is called CLG LookUps. Basically, what I need to do
is move the cell focus in Company Level Gaps from one cell to another
based on what is entered in the cell. For example, if in cell E6, Yes
is answered then the focus of the cell needs to be shifted to I6. Then,
if the entry in I6 is No, then the focus needs to change to LI6 and
then based on info in L6, the focus is some other cell. All this needs
to be continuous. CLG LookUps table has the info about what the cell
focus should be, based on the answer Yes or No. I have written the code
as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim currentcell

Select Case Range("E6").Value
Case "Yes"
currentcell = Sheets("CLG LookUps").Range("B2").Value ' currentcell
is I6
Range(currentcell).Select
Case Else
currentcell = Sheets("CLG LookUps").Range("C2").Value ' currentcell
is F6
Range(currentcell).Select
End Select

The problem I am having is that I don't know how to write the code to
change the focus to some other cell, once the focus cell is I6 or F6. I
basically, don't know how to make the cases flow. I wrote as follows,
but that doesn't work because there are two case statements. I'm not
sure how to merge the two case statements to make the focus move
continuously:

Select Case Range("E6").Value
Case "Yes"
currentcell = Sheets("CLG LookUps").Range("B2").Value
Range(currentcell).Select
Case Else
currentcell = Sheets("CLG LookUps").Range("C2").Value
Range(currentcell).Select
End Select


Select Case Range("F6").Value
Case "Yes"
currentcell = Sheets("CLG LookUps").Range("B13").Value
Range(currentcell).Select
Case Else
currentcell = Sheets("CLG LookUps").Range("C13").Value
Range(currentcell).Select
End Select

I hope this is not very confusing. Please help.

Vidhi

some like this !

Private Sub Worksheet_Change(ByVal Target As Range)

If [E6] = "Yes" Then [I6].Select
If [I6] = "No" Then [L6].Select

End Sub