code to lock only certain rows when criteria is met

[Ellie013]

I have a workbook with 11 sheets. Sheet 6 contains a lot of information that is sent to payroll on a regular basis that contains details of extra hours worked for a lot of workers and so is updated frequently. The sheet is protected so that users can only edit columns F and H across to V as the other columns contain lookup formulas. I need something that will colour the entire row and then lock it so that it cannot be edited if column V contains the word "sent" but will still allows other rows to be edited as per the range above. This is so that data that has been checked and sent to payroll cannot be edited. I have looked around and this https://www.mrexcel.com/forum/excel-questions/797241-lock-entire-row-based-cell-specific-text.html looked hopeful but wasn't quite right in that it locked the entire sheet down



Also I already have a code that means everytime the workbook is opened it always opens on sheet 11.

Private Sub Workbook_Open()

 

Worksheets("Remember").Activate

 

End Sub

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the word "Sent" in any cell in column V and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("V:V")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    If Target = "Sent" Then
        Rows(Target.Row).EntireRow.Interior.ColorIndex = 6
        Rows(Target.Row).EntireRow.Locked = True
    End If
    ActiveSheet.Protect
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

 

[Ellie013]

@mumps;5148401

This locks the whole sheet down still, I need to be able to edit certain parts of the sheet as before with only those rows being locked for editing

 

[mumps]

You will have to unlock all the cells in columns F and H:V before protecting it. Then try the macro again.