Showing/Highlighting Protected Cells

derekpegg

Board Regular
Joined
Oct 7, 2005
Messages
145
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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 :

Code:
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

Board Regular
Joined
Oct 7, 2005
Messages
145
Just used your code and I have to say that that was exactly what I was looking for - thanks again.
 

Forum statistics

Threads
1,144,522
Messages
5,724,830
Members
422,581
Latest member
samiak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top