Protect cell from editing based on another cells

[vimal606]

Dear Team,

I have an excel sheet which is filled by the employees.
Range "B" and "C" have data validation as list set to Yes/ No.

If "B" and "C" are marked as "NO", Range "D:F" should be protected from editing.
The event should be triggered on Cell value change.

Can this be achieved?

 

[baitmaster]

Yes, but only using VBA, and likely to be complex and still not fool proof

Do you really need to prevent this? Can you just make do with error warnings?

 

[vimal606]

Error warnings are fine but the range i am talking about contains data validations and lengthy formulas.
Thats the reason why i am looking for a VBA solution.

 

[baitmaster]

This is not a perfect piece of code and you may need to amend it, but it should show the principles

In the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
protectColumns Target
End Sub

In a general code module:

Sub protectSheet(sht As Worksheet)
    sht.Protect userinterfaceonly:=True
    sht.EnableSelection = xlUnlockedCells
End Sub

Sub protectColumns(rngChanged As Range)

Dim rngProtect As Range, rngUnprotect As Range
Dim cl As Range, iRow As Long

With Sheet1
    For Each cl In rngChanged
        If cl.Column = 2 Or cl.Column = 3 Then
            
            iRow = cl.Row
            
            If UCase(Cells(iRow, 2)) = "NO" And UCase(Cells(iRow, 3)) = "NO" Then
                If rngProtect Is Nothing Then
                    Set rngProtect = Range(.Cells(iRow, 4), .Cells(iRow, 6))
                Else
                    Set rngProtect = Union(rngProtect, Range(.Cells(iRow, 4), .Cells(iRow, 6)))
                End If
            
            Else
                If rngUnprotect Is Nothing Then
                    Set rngUnprotect = Range(.Cells(iRow, 4), .Cells(iRow, 6))
                Else
                    Set rngUnprotect = Union(rngUnprotect, Range(.Cells(iRow, 4), .Cells(iRow, 6)))
                End If
            
            End If
            
        End If
    Next cl
End With

If Not rngUnprotect Is Nothing Then
    rngUnprotect.Locked = False
    rngUnprotect.Interior.ColorIndex = 4
End If

If Not rngProtect Is Nothing Then
    rngProtect.Locked = True
    rngProtect.Interior.ColorIndex = 3
End If
protectSheet Sheet1
End Sub

Note, I assume the worksheet this is taking place in has the VBA code name "Sheet1". I'm using colour so you can see which ranges change and when

 

[vimal606]

Thank you