How to protect sheet allowing grouping and ungrouping but also allowing changing colour of specific cells

How to protect sheet allowing grouping and ungrouping but also allowing changing colour of specific cells

Post by Anurag Kot » Wed, 14 Oct 2009 20:35:04


I am preparing a template where there are few sheets having
functionality of grouping and ungrouping of rows. I am using below
code for protecting and is working well:

Private Sub Worksheet_Activate()
With Me
.EnableOutlining = True
.Protect "XXX", UserInterfaceOnly:=True
End With
End Sub

Now in a specific sheet I also want to provide user the ability to
change the colour of the specific cells.

Please let me know how to update the above code to support the

Any support is much appreciable !!!


1. How can I allow group ungroup while sheet is protected?

2. Protection - Allow Group/Ungroup but lock / unlock some cells

Hi Everyone

I have a spreadsheet which allows people to enter some figures in some cells.
Those cells then add together to creat bew totals.

I want to allow them to see all the individual lines or allow them to use
the group +or - (the rows have already been grouped) to change the views.

But, if I lock some cells in those rows, the Group + or - no longer works.

Is there anyway around? I've tried "Allow Format rows" as a protection
option but it didn't work. I've tried to select the whole worksheet, unlocked
it, then locked smalls parts, that didn't work either.

If there is a solution, I'd love to hear from you!

3. protect cell formula but allow changing bg color

4. cell protection to allow GROUP/UNGROUP function

5. How to protect a shared sheet but allow groups to expand

6. protect worksheet but allow users to use specific columns/cells

7. how to protect sheet but allow grouping

8. Allowing comments in unlocked cells within protected sheet

9. Allowing users to add comments to cells on protected sheets

10. Locking cell color while allowing data changes in cell

11. Allowing editing locked cells in a protected sheet in excel

12. allow comments, unlocked cells, protected sheet

13. Can I allow cell merging in a protected sheet

14. How can I protect Excel Worksheet and allow users to UnGroup Data

15. Allow unprotected cells to spell check after sheet is protected