If you have ever used cell protection on a worksheet (tab Review – Protect Sheet or in older versions of Excel – menu Service – Protection – Protect Sheet), you may have encountered this problem. As you know, this particular cell on the sheet will be protected from changes after the sheet protection is enabled, or not – is determined by the checkbox Protected cell (Locked) in the dialog box Cell Format (Format Cells) tab Protection (Protection):
In the case of applying sheet protection to a large and complex table or screen form, where there should be many input areas, it is not always clear – which cells on the sheet have this checkbox already turned off, and which ones still remain on? The macros published below just allow you to turn on / off color highlighting for unprotected cells on the current sheet so that they can be clearly seen.
To add these macros to the current workbook:
- press the keyboard shortcut ALT + F11to open the Visual Basic Macro Editor
- insert a new empty module into the book using the menu command Insert – Module
- copy and paste the code of the macros below
Public Fills Sub Unprotected_Cells_Show() Application.ScreenUpdating = False ReDim Fills(1 To ActiveSheet.UsedRange.Rows.Count, 1 To ActiveSheet.UsedRange.Columns.Count) For Each cell In ActiveSheet.UsedRange If cell.Interior.ColorIndex = -4142 Then Fills(cell.Row, cell.Column) = 0 Else Fills(cell.Row, cell.Column) = cell.Interior.Color End If If Not cell.Locked Then cell.Interior.ColorIndex = 3 Next cell Application.ScreenUpdating = True End Sub Sub Unprotected_Cells_Hide() Application.ScreenUpdating = False For Each cell In ActiveSheet.UsedRange If Fills(cell.Row, cell.Column) = 0 Then cell.Interior.ColorIndex = -4142 Else cell.Interior.Color = Fills(cell.Row, cell.Column) End If Next cell Application.ScreenUpdating = True End Sub
As you might guess, the first of these two macros turns on red highlighting for unprotected cells, and the second turns it off, restoring the original fill color. You can run these macros by pressing ALT+F8 or using the button Macros (Macros) tab developer (Developer).
The operation of the macro on the screen form example looks something like this:
And you don’t have to rack your brains and check where you removed the protection from the cells, and where it remained.
- How to protect individual cells on a sheet, the entire sheet, encrypt an Excel workbook?
- What are macros and how to use them?