SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2266287%22%20slang%3D%22en-US%22%3ESkip%20a%20cell%20based%20on%20value%20in%20a%20cell%20automatically%20(Private%20Sub)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2266287%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20per%20below%20Image%2C%20once%20I%20enter%20data%20in%20G6%2C%20automatically%20E9%20will%20select%20because%20of%20the%20code%20below.%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20wanted%20to%20skip%20this%20cell%20as%20it%20belongs%20to%20Friday%2C%20which%20is%20a%20holiday%20for%20the%20service%2C%20and%20to%20select%20E12%20instead.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sameer_Kuppanath_Sulta_1-1618133116611.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F271729i7F2A727E03614E32%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Sameer_Kuppanath_Sulta_1-1618133116611.png%22%20alt%3D%22Sameer_Kuppanath_Sulta_1-1618133116611.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20code%20below.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%0A%20%20%20%20%20If%20Not%20Intersect(Target%2C%20Me.Range(%22e%3Af%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20Target.Offset(0%2C%201).Activate%0A%20%20%20%20%20ElseIf%20Not%20Intersect(Target%2C%20Me.Range(%22g%3Ag%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20Target.Offset(3%2C%20-2).Activate%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2266287%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2266358%22%20slang%3D%22en-US%22%3ERe%3A%20Skip%20a%20cell%20based%20on%20value%20in%20a%20cell%20automatically%20(Private%20Sub)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2266358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sultan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20d%20As%20Long%0A%20%20%20%20If%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%0A%20%20%20%20If%20Not%20Intersect(Target%2C%20Me.Range(%22E%3AF%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Target.Offset(0%2C%201).Activate%0A%20%20%20%20ElseIf%20Not%20Intersect(Target%2C%20Me.Range(%22G%3AG%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20If%20Weekday(Target.Offset(0%2C%20-6).Value)%20%3D%20vbThursday%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20d%20%3D%206%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20d%20%3D%203%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Target.Offset(d%2C%20-2).Activate%0A%20%20%20%20End%20If%0AEnd%20Sub%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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