VBA - Locking a range of cells for each row based on a cell in the same row

Hi NathanA
I don't follow your point about the message
Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("E:G")) Is Nothing Then
    Exit Sub
End If
MsgBox "There's no need for data in this cell" & Target.Address
Me.Unprotect
For i = 1 To LR
        If Range("C" & i).Value = "Charter (lite)" Then
            Range("N" & i & ":W" & i).Locked = True
        Else
            Range("N" & i & ":W" & i).Locked = False
        End If
Next i
Me.Protect
End Sub

Hi @ NathanA

I'm sorry, I was testing on column E->G instead of N->W, just to have them closer...my mistake. So change that line to:

If Intersect(Target, Range("N:W")) Is Nothing Then

Now, whenever you change the value of any cell in columns N through W, the code will be triggered.
Remember that you need an "exact match" for the text in C(i): "Charter (lite)" to lock the cells in that row(i).

Perhaps you can be interested in a small variation, and trigger the code whenever the text in C(i) STARTS with the text "Charter", then you can change the line:

If Range("C" & i).Value = "Charter (lite)" Then

to this:

If Range("C" & i).Value Like "Charter*" Then

Hi @NathanA
Quite a long time with no answers...eh?

When you start a new sheet, all the cells are as default "locked", so if you protect the sheet, you cannot write in any cell.
I've added a new line to unlock all the cells in the active range before doing the work
In the code, I used the word "charter" to compare and trigger the action..... you must change this word to the right expression.
Then all the rows that have "charter" in column C will be locked between columns N to W
please try this new code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
Debug.Print LR
If Intersect(Target, Range("C:C")) Is Nothing Then
    Exit Sub
End If

Me.Unprotect
Me.UsedRange.Locked = False
For i = 1 To LR
        If Range("C" & i).Value = "charter" Then
            Range("N" & i & ":W" & i).Locked = True
        Else
            Range("N" & i & ":W" & i).Locked = False
        End If
Next i
Me.Protect
End Sub