How to lock specific row if a certain value exists in one cell?

[Rajesh Kr Joshi]

Hi,

How do I lock a row, if in column C a certain value exists?
In the attached sheet I have a daily employee attendance sheet, if an employee left or resigned the same is shown in column C using a formula. Is there a way to lock the specific row if it has a value Left or Resigned?



Thank
Rajesh

[xander1981]

Hi Rajesh,

I have put the following code together with should work but VBA isn't my best subject . The problem is going to be when you want the code to run and also in order for locked cells to take effect the workbook must be protected which yours is not. Left me know what you think and we can change the code to suit.

Sub LockNonactive()

Dim nonAct As Range
Dim CheckValue As String

Set nonAct = ActiveSheet.Range("C3:C10")
CheckValue = "Left"
CheckValue = "Resigned"

ActiveSheet.Unprotect

For Each Cell In nonAct
If Cell.Value = CheckValue Then Cell.EntireRow.Locked = True
Next

ActiveSheet.Protect


End Sub

[rollis13]

Just a little fix to xander1981's code:

Sub LockNonactive()
    Dim nonAct As Range
    Dim CheckValue1 As String
    Dim CheckValue2 As String
    Dim cell As Range
    Set nonAct = ActiveSheet.Range("C3:C10")
    CheckValue1 = "Left"
    CheckValue2 = "Resigned"
    ActiveSheet.Unprotect
    For Each cell In nonAct
        cell.EntireRow.Locked = False
        If cell.Value = CheckValue1 Or cell.Value = CheckValue2 Then cell.EntireRow.Locked = True
    Next
    ActiveSheet.Protect
End Sub

[Rajesh Kr Joshi]

Hi Xander and Rollis,

Thanks for your help. It worked

Thanks
Rajesh