Showing/Highlighting Protected Cells

[derekpegg]

Not sure if this is possible, but I have a pretty large workbook in which most of the cells are protected. Around 1000 probably are not for the user to enter relevant data. I am now testing the spreadsheet ready for use and wondered if there is a way (or addin?) to have either the protected or unprotected cells highlighted temporarily so I can check the correct cells are protected without having to select and check each one or many group of cells. Thanks for any help.

 

[DominicB]

Good afternoon derekpegg

You can download my add-in via the link below which can do this for you. Once installed go to Ultimate > Protection > Find Unlocked Cells. This will not colour the cells, just select all unlocked cells. If you wou prefer to use a macro, the code below is what is used in the add-in :

Sub UnlockCell()
If ActiveSheet Is Nothing Then Exit Sub
Dim Count, rng, Unlocked, Msg, Response
If Selection.Cells.Count = 1 Then
MsgBox "You must select a range before running this utility." & Chr(13) & "Please select a range and run the routine again.", , "Ultimate Add-In : Find Unlocked Cells"
Exit Sub
End If
Count = 0
On Error Resume Next
For Each rng In Selection
If rng.Locked = False Then
Count = Count + 1
If Count = 1 Then Set Unlocked = rng
If Count <> 1 Then Set Unlocked = Union(Unlocked, rng)
End If
Next rng
Msg = "cells"
If Count = 1 Then Msg = "cell"
If Count = 0 Then
MsgBox "There are no unlocked cells in the range " & Selection.Address, , "Ultimate Add-In : Find Unlocked Cells"
Exit Sub
End If
Response = MsgBox("Excel has counted " & Count & " unlocked " & Msg & " in the range " & Selection.Address & "." & Chr(13) & Chr(13) & "Would you like Excel to auto select the unlocked " & Msg & "?", vbYesNo, "Ultimate Add-In : Find Unlocked Cells")
If Response = vbYes Then
Unlocked.Select
End If
End Sub

HTH

DominicB

 

[derekpegg]

Sounds ideal Dominic. Will give it a try. Thanks a lot.

 

[derekpegg]

Just used your code and I have to say that that was exactly what I was looking for - thanks again.