CODEWITHSUNDEEP
vbaexcel
nina
nina

Reputation: 37

how to use the vb code for the entire column based on the condition

I want to disable one cell in excel based on the condition of the previous cell, for example, if A3="xyz" then B3= disable, and I have achieved this by the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target <> Range("A3") Then Exit Sub

    ActiveSheet.Unprotect

    If Range("A3").Value = "CNS" Then
        Range("B3").Locked = True
    End If

    If Range("A3").Value = "APL" Then
        Range("B3").Locked = False
    End If

    ActiveSheet.Protect

End Sub

Now I want to use this code for the entire A column. If I enter A4 or A5, then the corresponding B4 or B5 should become disabled.

Upvotes: 3

Views: 90

Answers (1)

R3uK
R3uK

Reputation: 14537

This should work just fine :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False

    If Not Application.Intersect(Target, Me.Columns(1)) Is Nothing Then
        ActiveSheet.Unprotect
        Select Case UCase(Target.Value)
            Case Is = "CNS"
                Target.Offset(0, 1).Locked = True
            Case Is = "APL"
                Target.Offset(0, 1).Locked = False
            Case Else
                MsgBox "Value not covered by the program", vbInformation + vbOKOnly
        End Select
        ActiveSheet.Protect
    Else
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 3

Related Questions