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

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

---
Regards,
Norman



"Fred Evans" wrote in message
om...
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

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.

F5SpecialConstants. Choose what you want to select and OK.

EditClear 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

On 30 Nov 2004 23:41:46 -0800, (Fred Evans) wrote:

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

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




"Norman Jones" wrote in message
...
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

---
Regards,
Norman



"Fred Evans" wrote in message
om...
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

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




"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.

F5SpecialConstants. Choose what you want to select and OK.

EditClear 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

On 30 Nov 2004 23:41:46 -0800, (Fred Evans)
wrote:

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

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" wrote in message
...
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.


---
Regards,
Norman



"Fred Evans" wrote in message
...
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




"Norman Jones" wrote in message
...
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

---
Regards,
Norman



"Fred Evans" wrote in message
om...
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

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.


---
Regards,
Norman



"Fred Evans" wrote in message
...
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




"Norman Jones" wrote in message
...
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

---
Regards,
Norman



"Fred Evans" wrote in message
om...
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

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



"Fred Evans" wrote in message
...
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




"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.

F5SpecialConstants. Choose what you want to select and OK.

EditClear 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

On 30 Nov 2004 23:41:46 -0800, (Fred Evans)
wrote:

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

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


"Norman Jones" wrote in message
...
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



"Fred Evans" wrote in message
...
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




"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.

F5SpecialConstants. Choose what you want to select and OK.

EditClear 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

On 30 Nov 2004 23:41:46 -0800, (Fred Evans)
wrote:

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

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



"Fred Evans" wrote in message
...