Color in protected sheets

[Scott Rodman]

On my computer at work, unprotected cells appear in blue, while protected
cells appear in black. I like this feature, but I can't get my Excel program
at home to do this. I'm using Excel 97 SR-2. Does anyone know how I can get
the same color effect I can at work?

Thanks!!

--

Scott Rodman
ar...@flash.net

[Leonard E. Meads]

Go to the cells you want to color and apply Format | Cells | Patterns with
the color of your choice.

BTW, I noticed that you posted your article via an ISP's Usenet server.
IMO, for best results when using the MS public NGs, participants should
connect directly to the MS public server <msnews.microsoft.com> rather than
via a Usenet server or web-based news service. Messages posted via these
latter 2 may not appear for up to 5-6 days, and may not appear at all, on
the MS server.

--
Len Meads
mea...@sprynet.com

************************************************************
For best connection to MS public NGs, connect
directly to MS public server <msnews.microsoft.com>
************************************************************

Scott Rodman <ar...@flash.net> wrote in article
<0F5L2.65$u_5...@news.flash.net>...

[Pernesz, Stephen]

Scott,
Use Conditional Formatting, formula is and insert this formula, then
select the colour under pattrens.
=IF(CELL("protect")=1,TRUE,FALSE)

[Pernesz, Stephen]

Sorry, forgot the other half.......
=IF(CELL("protect")=1,TRUE,FALSE) will colour protected cells and
=IF(CELL("protect")=0,TRUE,FALSE) will colour unprotected cells.
Hope this helps
Regards,
Stephen Pernesz

Wang Global - Contract Administration.
Austlink Corporate Park
2 Minna Close
Belrose NSW 2085

Phone: 02-9847 7659
Fax. : 02-9898 6003 Email: Stephen...@wang.com

[Pernesz, Stephen]

Pat,
My apologies, left of the cell reference in the formulas, they should
read :
=IF(CELL("protect",A1)=1,TRUE,FALSE) for protected cells
=IF(CELL("protect",A1)=0,TRUE,FALSE) for unprotected cells
Put both formulas in the conditional formatting for a cell (in this case
"A1") and then copy "A1" to all the cells where you want this condition.
the only drawback is that you will have to unprotect the cells which you
don't want locked after you have applied the conditional formatting.

Regards,
Steve

[Leonard E. Meads]

Why bother with Conditional Formatting?

A user must know whether a specific cell is locked (protected) or not
because he must set it to be unlocked using Format | Cells | Protection
(default is locked). All cells can be selected first and directly colored
as locked. Then, when unlocking a cell, the user can also apply a different
color to the now unlocked cell using Format | Cells | Patterns and avoid
the unnecessary overhead of all those conditional formats.

If a user has a worksheet created by someone else, or one in which he has
forgotten which cells are protected or not, then CELL can be useful in
determining which are locked and unlocked cells. But this can be done
without using a conditional format, and colors can then be applied
directly.

If a user changes a cell from locked to unlocked, or vice versa, then a
conditional format would pick this up. However, since the user must reset
the protection status in the Format Cells dialog box, he can reset the
cell's color at the same time since it's in the adjacent tab in the dialog
box. Again, no real need, IMO, for a conditional format to sit and wait for
something to do while eating up resources.

BTW, there was no need to put the CELL test inside an IF statement for
Conditional Formatting to work as you intended.

--
Len Meads
mea...@sprynet.com

************************************************************
For best connection to MS public NGs, connect
directly to MS public server <msnews.microsoft.com>
************************************************************

Pernesz, Stephen <Stephen...@wang.com> wrote in article
<5AC2269E14CED111B06E00805FD6292B4F3770@ASYDNT03>...

> Sorry, forgot the other half.......
> =IF(CELL("protect")=1,TRUE,FALSE) will colour protected cells and
> =IF(CELL("protect")=0,TRUE,FALSE) will colour unprotected cells.
> Hope this helps
> Regards,
> Stephen Pernesz
>
> Wang Global - Contract Administration.
> Austlink Corporate Park
> 2 Minna Close
> Belrose NSW 2085
>
> Phone: 02-9847 7659
> Fax. : 02-9898 6003 Email: Stephen...@wang.com
>
>

[Jeffrey McAhren]

I put use the following code to format all spreadsheets that I create for
other users. The users have indicated that they appreciate the consistancy
and the color coding makes their jobs easier because they know where they
should key data, and where not to key data.

You could add font formatting, etc. to this sub. You could put this code in
your personal macro workbook and assign a shortcut key like ctrl+shft+s.

Sub SetSelectionProtectionAtributes() 'Set cell shading for selection
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
If cell.Locked Then
cell.Interior.ColorIndex = xlNone 'white
Else '(not locked)
cell.Interior.ColorIndex = 35 'lt. green
End If
Next cell
Application.ScreenUpdating = True
End Sub

Sub ShadeBooksCells() 'Set shading for all sheets in the workbook
Dim Sheet As Worksheet
For Each Sheet In Sheets
Sheet.Activate
ActiveSheet.UsedRange.Select
SetSelectionProtectionAtributes
Range("a1").Select
Next Sheet
Beep
End Sub

Scott Rodman <ar...@flash.net> wrote in message
news:0F5L2.65$u_5...@news.flash.net...