SOLVED

Skip a cell based on value in a cell automatically (Private Sub)

Frequent Contributor

Hi.

 

 

As per below Image, once I enter data in G6, automatically E9 will select because of the code below. 

But I wanted to skip this cell as it belongs to Friday, which is a holiday for the service, and to select E12 instead. 

 

 

Sameer_Kuppanath_Sulta_1-1618133116611.png

 

 

 

I have the code below.

Private Sub Worksheet_Change(ByVal Target As Range)

     If Not Intersect(Target, Me.Range("e:f")) Is Nothing Then
          Target.Offset(0, 1).Activate
     ElseIf Not Intersect(Target, Me.Range("g:g")) Is Nothing Then
          Target.Offset(3, -2).Activate
        End If
        
    
        
End Sub

 

1 Reply
best response confirmed by Sameer_Kuppanath_Sultan (Frequent Contributor)
Solution

@Sameer_Kuppanath_Sultan 

Like this:

 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim d As Long
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Me.Range("E:F")) Is Nothing Then
        Target.Offset(0, 1).Activate
    ElseIf Not Intersect(Target, Me.Range("G:G")) Is Nothing Then
        If Weekday(Target.Offset(0, -6).Value) = vbThursday Then
            d = 6
        Else
            d = 3
        End If
        Target.Offset(d, -2).Activate
    End If
End Sub