Lock certain cells in the same row based on updates by a formula

[intensified89]

I'm trying to lock certain cells within the same row whenever one of the cell is being updated by a formula. This will be repeated for multiple rows. Currently I have the following macro, but it only allows the update whenever the user manually inputs, but doesn't work for updates by formula.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ActiveSheet.Unprotect
   
    If Not Intersect(Target, Range("AM:AM")) Is Nothing Then Exit Sub

    Select Case Target.Value
        Case "L"
            Range("T" & Target.Row & ":AA" & Target.Row).Locked = True
            Range("AG" & Target.Row & ":AH" & Target.Row).Locked = True
        Case Else
            Range("T" & Target.Row & ":AA" & Target.Row).Locked = False
            Range("AG" & Target.Row & ":AH" & Target.Row).Locked = False
    End Select
    
    ActiveSheet.Protect UserInterfaceOnly:=True, AllowFiltering:=True
    
End Sub

 

[intensified89]

Thanks. Strangely I get the Run-time error "13": Type mismatch for the following:

       If cel.Value = "L" Then

 

[gordsky]

No reason a mismatch should occur that I can see but If you havent resolved this then try changing

DIM cel as range

to

DIM cel as variant

OR do the follwoing
before the IF cel.vale line add this

msgbox cel.value & " " & cel.address

and then run the code again.
Alternatively if you are able to post a sample sheet it would help

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Lock certain cells in the same row based on updates by a formula - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.