Lock formula but allow colouring of the cell?

Posted on


Can anyone tell me how to lock a formula in a cell but allow the users to format the colour of the cell?


Yes, you must protect the sheet, but allow formatting.

Go to the review tab, changes group and select protect sheet.

The third box down in the new window is format cells, so place a check mark in this box to allow users to format the cells.

This simple VBA code will help you to Lock Sheet with Password but allow to apply all type of formatting to Rows & Columns.

Sub ProtectButAllowFormatting()
Sheets("Sheet1").Protect Password:="AA", DrawingObjects:=False, AllowFormattingCells:=True
End Sub

How it works:

  • Copy & Paste this code as Standard Module.
  • Run Macro to Protect Sheet.
  • Apply Format to Rows/Columns.
  • If need Unprotect Sheet using Password.
  • Alter Sheet Name & Password, if & when you

Leave a Reply

Your email address will not be published. Required fields are marked *