Lock some cells in row based on value in column A

[libcat]

Hi everyone,

Can anyone help me come up with a VBA code? I've tried a million ideas that I found on mrexcel but can't seem to get them to work.

I need to unlock specific cells in a row based on a specific value in column A of the same row:

If A2 = In Process, unlock B2:C2, E2, and G2:AN2
And repeat for each row: If A3 = In Process, unlock B3:C3, E3, and G3:AN3.

If the value in column A value is any other value that "In Process", all cells in that row should stay locked.

Thanks in advance!

 

[mohadin]

Hi
Welcome to the forum
Try

Sub test()
    Dim i As Long
    Dim arng As Range
    Dim rng As Range
    ActiveSheet.Unprotect ""
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) = "In Process" Then
            Set rng = Union(Range(Cells(i, 2), Cells(i, 3)), Cells(i, 5), Range(Cells(i, 7), Cells(i, 35)))
            If arng Is Nothing Then Set arng = rng Else Set arng = Union(arng, rng)
        End If
    Next
    arng.Locked = False
    ActiveSheet.Protect ""
End Sub

 

[libcat]

Thanks mohadin! I've tried this code and it works, but only if I go back to VBA and hit run after setting the value in column A to In Process. Do you know how I can make it automatically run after adjusting the value in A?

 

[mohadin]

Ok
Copy this code in sheet code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim i As Long
    Dim arng As Range
    Dim rng As Range
    ActiveSheet.Unprotect ""
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) = "In Process" Then
            Set rng = Union(Range(Cells(i, 2), Cells(i, 3)), Cells(i, 5), Range(Cells(i, 7), Cells(i, 35)))
            If arng Is Nothing Then Set arng = rng Else Set arng = Union(arng, rng)
         End If
    Next
    arng.Locked = False
    ActiveSheet.Protect ""
End Sub

 

[libcat]

I'm getting this error message:
Run-time error '91': Object variable or With block variable not set

Any idea what's causing that?

 

[libcat]

To be more specific- I get this error message when I set column A to anything other than "In Process".

 

[mohadin]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim rng As Range
    ActiveSheet.Unprotect ""
    If Not Intersect(Target, Range("a:a")) Is Nothing Then
        i = Target.Row
        Set rng = Union(Range(Cells(i, 2), Cells(i, 3)), Cells(i, 5), Range(Cells(i, 7), Cells(i, 35)))
        If Cells(i, 1) = "In Process" Then
            rng.Locked = False
        Else
            rng.Locked = True
        End If
        ActiveSheet.Protect ""
    End If
End Sub

 

[libcat]

Thank you so much for your help!!! It works perfectly now.

 

[libcat]

Hi mohadin... I was a bit premature with my last message. It seems like if ANY of the cells in column A contain "In Process", then cell in ALL rows are unlocked. I need it to only unlock the cells from the row with column A "In Process".
Thanks in advance!!

 

[libcat]

I think I've fixed it. I moved the
ActiveSheet.Protect ""
to after the last End If:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim rng As Range
    ActiveSheet.Unprotect ""
    If Not Intersect(Target, Range("a:a")) Is Nothing Then
        i = Target.Row
        Set rng = Union(Range(Cells(i, 2), Cells(i, 3)), Cells(i, 5), Range(Cells(i, 7), Cells(i, 35)))
        If Cells(i, 1) = "In Process" Then
            rng.Locked = False
        Else
            rng.Locked = True
        End If
    End If
    ActiveSheet.Protect ""
End Sub