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
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
Is this a duplicate post ??
Use this formula in CF to highlight protected cells
=CELL("protect",A1)=1
VBA Noob
_________________________________________
Credo Elvem ipsum etian vivere
_________________________________________
A message for cross posters
Please remember to wrap code.
Forum Rules
Please add to your signature if you found this link helpful. Excel links !!!
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
The Excel Options > Error Checking has an "Unlocked Cells containing formulas" option.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
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
Good afternoon ChemistB
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.Originally Posted by ChemistB
HTH
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
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
Or just use this code from Walkenbach site
Please Login or Register to view this content.
VBA Noob
Last edited by dominicb; 07-09-2008 at 07: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
HTHPlease Login or Register to view this content.
DominicB
Last edited by dominicb; 07-09-2008 at 07:25 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks