Global setting for Protected Cell Color?

[Kenneth Hobson]

The only thing close seems to be conditional formatting and it doesn't seem
to meet my needs.

I selected the whole sheet, set first box in Format | Conditional Formatting
to Formula Is and set the formula to be =cell("Protect",a1)=1 and set the
format as blue text. Since a1 is protected, all the cells become blue text.

Any ideas how to do this globally rather than a cell by cell format?

 

[JE McGimpsey]

For me, when I select the entire sheet, with A1 active, and set the CF
to be:

CF1: Formula is =Cell("protect", A1)
Format1: <font>/<blue>

Only the protected cells have blue text. If I then select, say, cell D9,
and choose Format/Conditional Format..., XL has indexed the formula to
read

CF1: Formula is =Cell("protect", D9)
Format1: <font>/<blue>

What happens to the formula when you do the same?

 

[Kenneth Hobson]

For me, when I select the entire sheet, with A1 active, and set the CF
to be:

CF1: Formula is =Cell("protect", A1)
Format1: <font>/<blue>

Only the protected cells have blue text. If I then select, say, cell D9,
and choose Format/Conditional Format..., XL has indexed the formula to
read

CF1: Formula is =Cell("protect", D9)
Format1: <font>/<blue>

What happens to the formula when you do the same?

I get all cells to be blue. Checking cell b1, the conditional format is:
=cell("Protect",B65507)=1 and blue text set. This is in Excel 9.0.2720 if
that makes a difference. Maybe it was a bug fixed in an update?

 

[JE McGimpsey]

Look closer at the directions I gave.

It looks to me like cell A31 was the active cell when you applied your
conditional format. Make sure you use the cell reference for the active
cell in your CF.

 

[Kenneth Hobson]

I had to use =cell("Protect",1)=0 to get what I needed. Thanks for the
replies.

I did try what you said JE about the active cell. In any case, I'm good to
go now.

I thought that I had tried =cell("Protect",1)=0. I was thinking that the
frozen panes was a factor. Don't see how now as I couldn't duplicate the
problem later.

 

[JE McGimpsey]

Are you sure? I get a syntax error when I try to enter that.

CELL() requires a reference as the second argument. A constant (1)
shouldn't work.

 

[Kenneth Hobson]

Sorry, JE. I meant:
=cell("Protect",A1)=0

Thanks for catching it.