Can I conditionally format a cell based on whether it is pro

Barry Katcher · Apr 24th, 2002, 06:45 AM

I'd like to format my workbooks so that the font color of entries to unprotected cells is blue and the font color to protected cells is black, similar to 1-2-3. Any way to do this? Thanx in advanx.

Big Blue · Apr 24th, 2002, 07:37 AM

When you go into 'FormatCells...' to unlock your selected cells, go into the FONT tab at the same time and select blue.

lenze · Apr 24th, 2002, 08:14 AM

If you wish to do this often, you might consider creating a cell "Style" for protected cells. Then just apply that "style" on selected cells. You can also apply "style" in VBA coding based on cell value,range,etc. Just a few seconds time saver.

Barry Katcher · Apr 24th, 2002, 08:36 AM

Thanx, Big Blue & lenze, but I would like this to automatically change the font color whenever I protect or unprotect any cell(s), similar to what Lotus does. Doesn't look possible, does it?

lenze · Apr 24th, 2002, 08:43 AM

Yes, but if you set up a style for unprotected cells, you simply change the style to protect and unprotect cells. Example:

Go to Format>Styles. You will see a name box displaying the style name of the active cell, usually normal. Type in a new name, such as 'protected' and click add. Then choose modify and select font color blue and unlock for protection. Now you can apply this style to cells you wish to unprotect and apply Normal to cells you wish to remain locked. You will no longer use the Format dialog to lock and unlock cells. Just toggle between the styles

[ This Message was edited by: lenze on 2002-04-24 07:43 ]

Barry Katcher · Apr 24th, 2002, 09:32 AM

Thanx, lenze. I'll try it right now.

Chris Davison · Apr 24th, 2002, 03:34 PM

Barry,

Globally format your font colour to black

then choose conditional format and try :

=CELL("protect",a1)=0 with the associated font format as your blue

you should find your (default) locked cells are black font, with any unlocked cells blue because of the conditional formatting kicking in

_________________
Hope this helps,
Chris

edit - apply conditional format with all sheets selected and all cells highlighted

[ This Message was edited by: Chris Davison on 2002-04-24 14:42 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 06:45 AM	#1
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	I'd like to format my workbooks so that the font color of entries to unprotected cells is blue and the font color to protected cells is black, similar to 1-2-3. Any way to do this? Thanx in advanx.

Apr 24th, 2002, 07:37 AM	#2
Big Blue Board Regular Join Date: Mar 2002 Location: Ontario Posts: 52	When you go into 'FormatCells...' to unlock your selected cells, go into the FONT tab at the same time and select blue.

Apr 24th, 2002, 08:14 AM	#3
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	If you wish to do this often, you might consider creating a cell "Style" for protected cells. Then just apply that "style" on selected cells. You can also apply "style" in VBA coding based on cell value,range,etc. Just a few seconds time saver.

Apr 24th, 2002, 08:36 AM	#4
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	Thanx, Big Blue & lenze, but I would like this to automatically change the font color whenever I protect or unprotect any cell(s), similar to what Lotus does. Doesn't look possible, does it?

Apr 24th, 2002, 08:43 AM	#5
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	Yes, but if you set up a style for unprotected cells, you simply change the style to protect and unprotect cells. Example: Go to Format>Styles. You will see a name box displaying the style name of the active cell, usually normal. Type in a new name, such as 'protected' and click add. Then choose modify and select font color blue and unlock for protection. Now you can apply this style to cells you wish to unprotect and apply Normal to cells you wish to remain locked. You will no longer use the Format dialog to lock and unlock cells. Just toggle between the styles [ This Message was edited by: lenze on 2002-04-24 07:43 ]

Apr 24th, 2002, 09:32 AM	#6
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	Thanx, lenze. I'll try it right now.

Apr 24th, 2002, 03:34 PM	#7
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Barry, Globally format your font colour to black then choose conditional format and try : =CELL("protect",a1)=0 with the associated font format as your blue you should find your (default) locked cells are black font, with any unlocked cells blue because of the conditional formatting kicking in _________________ Hope this helps, Chris edit - apply conditional format with all sheets selected and all cells highlighted [ This Message was edited by: Chris Davison on 2002-04-24 14:42 ]