Make Cell Range formattable while protected

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
137
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.

VBA Code:
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,200
Office Version
  1. 2016
Platform
  1. Windows
Is there any reason why you can't just use normal excel protection and allow only cell formatting?
 

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
137
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,329
Messages
5,641,545
Members
417,219
Latest member
FrancoisT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top