Protected cells -automatically format to a different color

[Fred Evans]

My old dos spreadsheet (supercalc 5) used to colour the text of
protected cells in a different color ,(which made the entry of data
easy because the protected cells usually contain formulae ).When
reentering new data I was then able to select the whole spreadsheet
and "blank " all cells that are not protected in one keystroke.
This left all cells that were protected with there formulae intact and
highlighted in a different color.

Can anyone suggest the way to do this in EXCEL?

The ideal solution would be to create a format or a style that will
automatically be used whenever a cell is protected.

To go throught the whole sheet manually reformatting each and every
protected cell seem to be a very long way around a relatively simple
problem.

Regards from South africa

 

[Norman Jones]

Hi Fred,

If you are not otherwise using conditional formatting, with all cells
selected, try:

Format | Conditional Formatting | Select 'Formula Is' in the first box |
Iin the second box enter the formula: =CELL("Protect",A1)=0 |
Select a format to suit | Ok| Ok

 

[Gord Dibben]

I was then able to select the whole spreadsheet

and "blank " all cells that are not protected in one keystroke.

Fred

This post does not address your "colored cells" issue but.........

CRTL + A to select all cells.

F5>Special>Constants. Choose what you want to select and OK.

Edit>Clear Contents

This will leave cells with formulas intact and ready for new data.

Not exactly one keystroke, but you could record a macro and assign it to a
button or shortcut key combo.

Gord Dibben Excel MVP

 

[Fred Evans]

Hello Norman-- thank you for the help but I am still battling a bit.

The Phrase:-
"=Cell ("protect"A1)=)"
Would you mind translating this to me in " plain english " so I can get the
logic??

Is the "code" in vb or where can I get a "translation" ?

I tried to enter this code and there was no result

Thanks
Fred

 

[Fred Evans]

Hello Gord

Thanks for the input.

1. I tried CTRL + A (hold down ctrl and A at the same time and nothing
happens?
sorry but I am a 62 yearold newbie to excel

2. Also F5 doesnt do anything

3. I am using excel 97

I am also having problem with understanding the terminolgy.
A. Am I correct in assuming that what I refer to as "protect" ( that the
cell or cells that are protected are not able to be overwritten) in Excell
language is refered to as "locked"
B. That there is no way to unprotect a single cell or a few cells - either
the whole worksheet is protected or the whole worksheet is unprotected
depending on the switch "protection" in "Tools". I cannot for example select
a few cells and just unprotect them temporarily so that I may change a
formula.? ( without unprotecting the whole sheet )

regards fred

I was then able to select the whole spreadsheet
and "blank " all cells that are not protected in one keystroke.

Fred

This post does not address your "colored cells" issue but.........

CRTL + A to select all cells.

F5>Special>Constants. Choose what you want to select and OK.

Edit>Clear Contents

This will leave cells with formulas intact and ready for new data.

Not exactly one keystroke, but you could record a macro and assign it to a
button or shortcut key combo.

Gord Dibben Excel MVP

 

[Fred Evans]

Thank you Norman --
I found my problem-- using the formula that you gave me the unlocked cells
are formatted
in adifferent color

I changed the 0 to a 1 and now I am cooking on gas--
all the protected cells are a different color.of text
I seem to be getting somewhere
regards fred

 

[Norman Jones]

Hi Fred,

The formula uses the versatile Excel Cell worksheet function - it is not a
VBA function.

CELL("Protect",A1)

returns 0 if A1 is locked and 1 if it is unlocked.


For full syntax and usage of the Cell function see Excel help.

 

[Norman Jones]

Hi Fred,

1) With Ctrl depressed, pressing A should select the entire worksheet. This
is true, AFAIK, in all versions except 2003 which requires that this key
combination be duplicated.
That notwithstanding, you can obtain the same functionality by clicking the
junction of the colum and row headers.

2) The F5 function key provides access to the GoTo dialog box. If this does
not work for you, perhaps you have a faulty key. Here, an alternative would
be Edit | Goto

3) In an unprotected sheet, locking cells only sets the cells' status to
locked. Protecting the sheet locks those cells whose status has previously
been set to locked..

The initial default condition for all cells is locked. Normally, the easiest
way of locking only selected cells is to select all cells (see 1) and
uncheck the locked option; then select the cells to be locked and check the
locked option.

Finally, if your intention is protect your formula cells, you can select
these in one operation by:

F5 (or Edit Goto) | Special | Check the 'Formulas' option | Ok


---
Regards,
Norman

Hello Gord

Thanks for the input.

1. I tried CTRL + A (hold down ctrl and A at the same time and nothing
happens?
sorry but I am a 62 yearold newbie to excel

2. Also F5 doesnt do anything

3. I am using excel 97

I am also having problem with understanding the terminolgy.
A. Am I correct in assuming that what I refer to as "protect" ( that the
cell or cells that are protected are not able to be overwritten) in Excell
language is refered to as "locked"
B. That there is no way to unprotect a single cell or a few cells -
either
the whole worksheet is protected or the whole worksheet is unprotected
depending on the switch "protection" in "Tools". I cannot for example
select
a few cells and just unprotect them temporarily so that I may change a
formula.? ( without unprotecting the whole sheet )

regards fred

 

[Fred Evans]

Thank you very much-- just a little story about why ctrl A didnt work
I have a computer at work and one at home
It works on the computer at work but not on the one at home
The one at home works if you say ALT A
I think the keys have been switched around!!!

This is great - Could you tell me how to save the protection format to be my
default in all my spreadsheets??

regards

Hi Fred,

1) With Ctrl depressed, pressing A should select the entire worksheet. This
is true, AFAIK, in all versions except 2003 which requires that this key
combination be duplicated.
That notwithstanding, you can obtain the same functionality by clicking the
junction of the colum and row headers.

2) The F5 function key provides access to the GoTo dialog box. If this does
not work for you, perhaps you have a faulty key. Here, an alternative would
be Edit | Goto

3) In an unprotected sheet, locking cells only sets the cells' status to
locked. Protecting the sheet locks those cells whose status has previously
been set to locked..

The initial default condition for all cells is locked. Normally, the easiest
way of locking only selected cells is to select all cells (see 1) and
uncheck the locked option; then select the cells to be locked and check the
locked option.

Finally, if your intention is protect your formula cells, you can select
these in one operation by:

F5 (or Edit Goto) | Special | Check the 'Formulas' option | Ok

 

[Norman Jones]

Hi Fred,

Could you tell me how to save the protection format to be my
default in all my spreadsheets??

Open the template file Book.xlt which should reside in your XLStart
directory. (The location of this directory varies depending on OS / XL
version, Iif necessary, perform a Windows search for the directory.)
Make any changes (e.g. to font) that you want as workbook defaults.
Save (without changing the file name or location) and close the file.

As for the protection format, are you certain that you want / need this in
*all* future workbooks? One big drawback to doing this is that you will
loose the very useful functionality of conditional formatting for other
purposes.

If this conditional format arrangement is one that you would often (but not
always) like, proceed as above but save the template file as (say)
Book_CF.xlt.
If you adopt this approach, your default workbooks will remain unchanged,
but opening a new workbook (via File | New) will open a dialog box which
will offer you the choice of the two template files.

---
Regards,
Norman



news:[email protected]...