Make Cell Range formattable while protected

[EBoudreau]

I'm looking to make cell range C9:C56 the only cells on the sheet where I can allow users to format the cell while protected and be able to change the cell colors in that range only to their liking. So far, with the given code below, all cells on the sheet are formattable while protected.

From there, I have a macro on a button that will copy that formatting to corresponding cells on the same page and to select cells on a another sheet. I've been playing around with it but can't figure out how to specify that specific range.

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim Rng As Range
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="WellingtonFrac"
    
    Set Rng = Intersect(Target, Range("A9:A20, A22:A34, A59:A61, F9:DA56, F59:DA82"))
    If Not Rng Is Nothing Then Call Capitalise(Rng)
        
    Set Rng = Intersect(Target, Range("D53:D1583"))
    If Not Rng Is Nothing Then Call ConvertToTime(Rng)
    
'    Rrange to automatically trigger recorded macro.
'    If No Intersect(Target, Range("C9:C32")) Is Nothing Then
'        Call CopyWellColors
'    End If
    
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, Password:="WellingtonFrac"
    Application.EnableEvents = True

    Sheets("Frac Report").EnableSelection = xlNoRestrictions
    Sheets("Stage Times").EnableSelection = xlNoRestrictions

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng As Range, RngRow As Range, RngCol As Range
Set Rng = Intersect(Target, Range("F9:DA56,F59:DA106"))
If Not Rng Is Nothing Then
    Set RngRow = Range("F7:DA7")
    Set RngCol = Range("D9:D56")
    RngRow.Interior.ColorIndex = 37 'xlColorIndexNone
    RngCol.Interior.ColorIndex = 15 'xlColorIndexNone
    RngRow(Target.Column - 5).Interior.ColorIndex = 4
    RngCol(Target.Row - 8).Interior.ColorIndex = 4
End If
Set Rng = Intersect(Target, Range("F59:DA106"))
If Not Rng Is Nothing Then
    Set RngRow = Range("E57:DA57")
    Set RngCol = Range("D59:D106")
    RngRow.Interior.ColorIndex = 37 'xlColorIndexNone
    RngCol.Interior.ColorIndex = 15 'xlColorIndexNone
    RngRow(Target.Column - 4).Interior.ColorIndex = 4
    RngCol(Target.Row - 58).Interior.ColorIndex = 4
End If
End Sub

 

[Zot]

Is there any reason why you can't just use normal excel protection and allow only cell formatting?

 

[EBoudreau]

Is there any reason why you can't just use normal excel protection and allow only cell formatting

I feel like when the sheet was originally like that I was running into problems, but cant recall what was going on.
I think what was happening was with the capitalization going on, the sheet had to be unlocked, then capitalization happened, then protect enabled.

I figured it would be pretty simple to just specify that cell range to be formattable and nothing else.
The only thing I wasn't sure of was how to replace AllowFormattingCells:=True to what I'm looking for.