VBA Lock Formula (given) Doesn't Allow Filtering/Highlighting/Font Change

[CuriousForge]

I do not how to write VBA codes, however this VBA formula I found on the internet allows me to lock cells after data entry as intended.

It doesn't allow Filtering cells, cell highlighting or font modification for protected cells. Can someone help me modify this so it allows filtering/highlighting cells/font modifications.

Private Sub Worksheet_Change(ByVal Target As Range)
Const strPassword = "secret"
Dim rngEdit As Range
Dim cel As Range
Set rngEdit = Intersect(Range("B2:B20,D2:D20"), Target)
If Not rngEdit Is Nothing Then
Application.EnableEvents = False
Me.Unprotect Password:=strPassword
For Each cel In rngEdit
cel.Locked = (cel.Value <> "")
Next cel
Me.Protect Password:=strPassword
Application.EnableEvents = True
End If
End Sub

 

[JackDanIce]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)

    Const strPassword = "secret"
    
    Dim rngEdit As Range
    Dim cel     As Range

    Set rngEdit = Intersect(Range("B2:B20,D2:D20"), Target)
    
    If Not rngEdit Is Nothing Then
        Application.EnableEvents = False
        
        Me.Unprotect Password:=strPassword
        
        For Each cel In rngEdit
            cel.Locked = (cel.Value <> "")
        Next cel
        
        Me.Protect Password:=strPassword, AllowFormattingCells:=True, AllowFiltering:=True
        
        Application.EnableEvents = True
    End If
    
End Sub

 

[CuriousForge]

IT WORKED! Thanks mate! Saved me a world of trouble.

 

[JackDanIce]

You're welcome, glad it's resolved!

 

[CuriousForge]

You're welcome, glad it's resolved!

Hi @JackDanIce can you please tell me how to add an Insert Rows/Edit Objects/Edit Scenarios permissions within this formula?
Basically things we can tick while protecting a sheet with a password.