Use of CELL("protect") in conditional formatting in Excel 07
 

I have seen this discussed before but have tried several formulas and have
not been able to do what I want to do. It is theoretically very simple: I
want to use conditional formatting on an entire worksheet, and for every cell
that is NOT locked, I want it to be shaded/filled with a color. I also have
values in my worksheet that are errors, and I read that conditional
formatting will not work with cells that have errors. How do I get around
this?

I tried:

=NOT(CELL("protect"))
=IF(CELL("protect") = "0",TRUE,FALSE)
=CELL("protect")

All with the 'applies to' field set to =$A$1:$I$35 (this is the effective
size of my worksheet) and the 'Stop if True' box unchecked.

Use of CELL("protect") in conditional formatting in Excel 07
 

You need to include the cell reference:

=CELL("protect",A1)=0

By default all cells are formatted as locked but they're not literally
locked until you apply sheet protection.

--
Biff
Microsoft Excel MVP


"Mark" wrote in message
...
I have seen this discussed before but have tried several formulas and have
not been able to do what I want to do. It is theoretically very simple: I
want to use conditional formatting on an entire worksheet, and for every
cell
that is NOT locked, I want it to be shaded/filled with a color. I also
have
values in my worksheet that are errors, and I read that conditional
formatting will not work with cells that have errors. How do I get around
this?

I tried:

=NOT(CELL("protect"))
=IF(CELL("protect") = "0",TRUE,FALSE)
=CELL("protect")

All with the 'applies to' field set to =$A$1:$I$35 (this is the effective
size of my worksheet) and the 'Stop if True' box unchecked.

Use of CELL("protect") in conditional formatting in Excel 07
 

The formula

=IF(CELL("protect",A1) = 0,TRUE,FALSE)

worked. Thanks.

"T. Valko" wrote:

You need to include the cell reference:

=CELL("protect",A1)=0

By default all cells are formatted as locked but they're not literally
locked until you apply sheet protection.

--
Biff
Microsoft Excel MVP


"Mark" wrote in message
...
I have seen this discussed before but have tried several formulas and have
not been able to do what I want to do. It is theoretically very simple: I
want to use conditional formatting on an entire worksheet, and for every
cell
that is NOT locked, I want it to be shaded/filled with a color. I also
have
values in my worksheet that are errors, and I read that conditional
formatting will not work with cells that have errors. How do I get around
this?

I tried:

=NOT(CELL("protect"))
=IF(CELL("protect") = "0",TRUE,FALSE)
=CELL("protect")

All with the 'applies to' field set to =$A$1:$I$35 (this is the effective
size of my worksheet) and the 'Stop if True' box unchecked.

Use of CELL("protect") in conditional formatting in Excel 07
 

You don't need the IF function but it will work equally as well.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mark" wrote in message
...
The formula

=IF(CELL("protect",A1) = 0,TRUE,FALSE)

worked. Thanks.

"T. Valko" wrote:

You need to include the cell reference:

=CELL("protect",A1)=0

By default all cells are formatted as locked but they're not literally
locked until you apply sheet protection.

--
Biff
Microsoft Excel MVP


"Mark" wrote in message
...
I have seen this discussed before but have tried several formulas and
have
not been able to do what I want to do. It is theoretically very simple:
I
want to use conditional formatting on an entire worksheet, and for
every
cell
that is NOT locked, I want it to be shaded/filled with a color. I also
have
values in my worksheet that are errors, and I read that conditional
formatting will not work with cells that have errors. How do I get
around
this?

I tried:

=NOT(CELL("protect"))
=IF(CELL("protect") = "0",TRUE,FALSE)
=CELL("protect")

All with the 'applies to' field set to =$A$1:$I$35 (this is the
effective
size of my worksheet) and the 'Stop if True' box unchecked.