Lock rows in a table while still being able to filter/sort.

[cmcev]

Hi,

I'm currently using the following code to lock Row's A:P. I have a dropdown in column R to select 'Yes'. Once this is selected I have conditional formatting changing the row to grey to show it shouldnt be edited. However in doing so, once the rows are locked I can no longer filter or sort the table.

Any advice would be great.

Thanks,
C

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E1")) Is Nothing Then
        ActiveSheet.Name = ActiveSheet.Range("E1")
    End If
    If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Yes"
            Range("A" & Target.Row & ":P" & Target.Row).Locked = True
            UserInterfaceOnly = True
            AllowSorting = True
            AllowFiltering = True
        Case Else
            Range("A" & Target.Row & ":P" & Target.Row).Locked = False
    End Select
End Sub