Highlighting Protected/Unprotected Cells

**ChemistB** · 01-28-2008, 09:05 AM

The Special GoTo function doesn't have a listing for highlighting (going to) only protected or (preferribly) unprotected cells. Is there an easy way to do this? Thanks!

ChemistB

**VBA Noob** · 01-28-2008, 09:48 AM

Is this a duplicate post ??

Use this formula in CF to highlight protected cells

=CELL("protect",A1)=1

VBA Noob

**ChemistB** · 01-28-2008, 10:31 AM

Hi VBA,

Nope, not a duplicate.

What I'm looking for is not a way to highlight in the conditional formatting sense. More like what the GoTo function does so I can see at a glance where I have unprotected Cells. I'd do this as part of my workbook validation and setting it up in Conditional formatting would require that I delete my previous conditional formatting. What I really need is for it to be able to be stored in my personal macro workbook.

Thanks again.

ChemistB

**mikerickson** · 01-28-2008, 10:47 AM

The Excel Options > Error Checking has an "Unlocked Cells containing formulas" option.

**ChemistB** · 01-28-2008, 11:08 AM

Alas, that starts with 2002 and I'm on 2000.

That's what I'm looking for though.

Anybody have any macros that do the same thing??

ChemistB

**dominicb** · 01-28-2008, 11:41 AM

Good afternoon ChemistB

Originally Posted by ChemistB

Anybody have any macros that do the same thing??

My free add-in, available via the link below, has this functionality. Once installed, highlight a range and go to Ultimate > Protection > Find Unlocked Cells. You will be told how many unlocked cells are in the range - click "yes" to auto-select them.

HTH

DominicB

**ChemistB** · 01-29-2008, 04:12 PM

Hi Dominic,

I downloaded your addin and it's very impressive. For me to use it at work might require a lot of fast talking but I think it's worth the try. Thanks again.

ChemistB

**VBA Noob** · 01-29-2008, 04:18 PM

Or just use this code from Walkenbach site

Please Login or Register  to view this content.

VBA Noob

**dominicb** · 01-29-2008, 04:26 PM

Hi ChemistB

Noob's stepped in there with a neat piece of code. Just for completeness, here's the code that drives my attempt. They're both pretty similar in that they both use the Union construct, which is the best way to select multiple cells. The main difference is that mine works on a predefined range, rather than the whole range : not necessarily right or wrong - it just might be a bit quicker if your UsedRange is monstrous

Please Login or Register  to view this content.

HTH

DominicB

**ChemistB** · 02-01-2008, 02:28 PM

Thanks to both of you. I have saved both codes to my workbook so that I can use either depending on the situation. I also came across another solution (modifying something from J. Rubin's books). I'll add it here so that anyone searching threads has a third option.

1. Go to Define names and select a name (e.g. LockedCells).
2. Refers to: =Get.Cell(14,INDIRECT("rc",FALSE)). Add and OK.
14 is the code for returning true or false to whether a cell is
locked or not.
3. Select range (or whole sheet)
4. Conditional Formatting Formula is =NOT(LockedCells)
Select a nice colorful format and OK

All unlocked cells will be formatted as per your conditional format.

Note, this overwrites any previous Conditional formatting so you wouldn't want to overwrite your file after performing this exercise.

ChemistB

Highlighting Protected/Unprotected Cells

LinkBack

Thread Tools

Display

Highlighting Protected/Unprotected Cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions