VBA Worksheet_Change not working

Diogo Melo · Jun 27th 2018

Hi there,

Could any kind soul help me, i have spent a considerable amount of time searching and trying to get these 2 codes to work but couldn't.
Is there any way i can combine these 2 codes? They serve 2 different purposes.

1.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Len(Target) = 10 Then
Range("I" & Target.Row & ":J" & Target.Row & ", K" & Target.Row & ", M" & Target.Row) = "N"
End If
End Sub

2.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 12 And Target.Value = "Y" Then
Target.Offset(0, 1) = Date
End If
End Sub

I tried to combine it into this

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Len(Target) = 10 Then
Range("I" & Target.Row & ":J" & Target.Row & ", K" & Target.Row & ", M" & Target.Row) = "N"
End If
If Target.Column = 12 And Target.Value = "Y" Then
Target.Offset(0, 1) = Date
End If
End Sub

But the 2nd code just wont work, i don't know whats wrong. Appreciate if anyone could assist in this!!

Mumps · Jun 27th 2018

Try:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,L:L")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 1
            If Len(Target) = 10 Then
                Range("I" & Target.Row).Resize(, 3) = "N"
                Range("M" & Target.Row) = "N"
            End If
        Case Is = 12
            If Target.Value = "Y" Then
                Range("I" & Target.Row).Resize(, 3) = "N"
                Range("M" & Target.Row) = "N"
            End If
    End Select
End Sub

Display More

VBA Worksheet_Change not working

Participate now!

Share