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!

 

[Joe4]

Alternatively, it might make sense to move BOTH inside the IF...THEN loop, as there is no reason to unprotect it if the condition is not met and aren't going to do anything.
The way you have it now will work, but will needlessly unprotect/protect it when nothing is being triggered to happen.

Here is what that undated code would look like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim rng As Range
    If Not Intersect(Target, Range("a:a")) Is Nothing Then
        ActiveSheet.Unprotect ""
        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]

@Joe4 Thanks, that's a good suggestion. I adjusted it and it's working as expected.

 

[mohadin]

@[SIZE=4][FONT=arial][COLOR=rgb(0, 0, 0)]libcat[/COLOR][/FONT][/SIZE]​

Glad I could help
And thank you for the feedback
Be happy and safe


@Joe4 Thanks