1

When i lock cells in excel How can i prevent users from editing the background color, text color, bold text etc in cells that are locked. But allow them to change background color etc in cells that are not locked?

When i protect the sheet all cells become uneditable regarding formatting.

Thanks in advance.

4
  • Allowing users to format cells is an all-or-nothing option. You can get around this with VBA. Is that an option for you? Jun 2, 2015 at 12:52
  • Absolutly could that be an option.
    – Behedwin
    Jun 3, 2015 at 5:15
  • All i want to do is to lock specific cells and then do mass edits on background on the other cells. I want to lock specific cells so they keep their originial background.
    – Behedwin
    Jun 3, 2015 at 5:16
  • You'll have to put code in the Worksheet_Change event to revert the cells you want to lock to their original formatting. Jun 3, 2015 at 13:08

1 Answer 1

1

This was my solution to the problem. It allows the user to click on the "Unlocked" cell and access the formatting. However when clicking on a "locked" cell or even straddling across both types the formatting option turns off again.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Target.Locked Then
    wsInfo.Protect Password:=gm_sPass, AllowFormattingCells:=True
  Else
    wsInfo.Protect Password:=gm_sPass, AllowFormattingCells:=False
  End If
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .