VBA to Lock all cells within a range except for a particular row range that matches a particular criteria??

[Sleeplol]

Hello,

Is there a way to lock all cells within a range except for a particular row range that matches a particular criteria??
For instance, if "3816" is in B1 then D8:K8 are "unlocked" and the user can adjust the cells per the data validation drop-down; but D4:K7 are locked.

Thanks for advice with this.

 

[breynolds0431]

Hello. That can be done with the following code.

Sub lockrow()

'General variable declarations
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim c As Range

'Will your sheet be password protected? If so, declare a password
Dim pWord As String: pWord = "Password"

'loop through range in B4 through B8
For Each c In ws.Range("B4:B8").Cells
    With ws
        'tests if current cell in loop range is equal to value in cell B1
        If c.Value = .Range("B1").Value Then
            'if sheet is password protected, unlock here with pWord
            'if not, then delete the below ".Unprotect pWord" line
            .Unprotect pWord
            .Range(.Cells(c.Row, 4), .Cells(c.Row, 11)).Locked = True
        Else
            'if sheet is password protected, unlock here with pWord
            'if not, then delete the below ".Unprotect pWord" line
            .Unprotect pWord
            .Range(.Cells(c.Row, 4), .Cells(c.Row, 11)).Locked = False
        End If
    End With
Next c

'if sheet is password protected, reapply the protection here
'if not, then delete the below "ws.Protect pWord" line
ws.Protect pWord

End Sub

 

[Sleeplol]

Hi Breynolds0431,

Thanks for jumping on this and making the notes so clean and clear.

But maybe you can let me know what I'm doing wrong.

The sheet isn't currently password protected so I've commented out the 4 password lines that you indicated.
But we I run the code, all of the rows in the range are still editable (not just the line that matched B1.

 

[breynolds0431]

Sorry, I did get the code backwards a bit after looking at it again. I had it locking the row with the match in B1. The below will lock all rows that don't match B1.

The reason that nothing locked for you previously is that for a cell to become truly locked, the sheet must be protected. You wouldn't have to lock it with a password if you don't want to, but it does need the sheet protection turned on. Please give the below a try, which does not use a password.

Sub lockrow()

'General variable declarations
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim c As Range

'loop through range in B4 through B8
For Each c In ws.Range("B4:B8").Cells
    With ws
        'tests if current cell in loop range is equal to value in cell B1
        If c.Value = .Range("B1").Value Then
            .Unprotect 'no password
            .Range(.Cells(c.Row, 4), .Cells(c.Row, 11)).Locked = False
        Else
            .Unprotect 'no password
            .Range(.Cells(c.Row, 4), .Cells(c.Row, 11)).Locked = True
        End If
    End With
Next c

'must enable sheet protection in order for the cells to be locked
ws.Protect 'no password

End Sub

 

[Sleeplol]

Awesome, thanks!