Shading Locked Cells

[Shrikant]

I want to shade / color locked cells to distinguish them from non-locked
cells. However I do not wish these shading or colors to be printed. It should
only for viewing on the screen. How to do it ?
--
Shrikant

[Shrikant]

Further Note
I want to shade / color automatically - e.g. by conditional formatting may be
--
Shrikant


"Shrikant" wrote:

> I want to shade / color locked cells to distinguish them from non-locked
> cells. However I do not wish these shading or colors to be printed. It should
> only for viewing on the screen. How to do it ?
> --
> Shrikant

[Dave Peterson]

Do you use Format|Conditional formatting?

If no, then you could do this:

Pick a cell that you can use to indicate that you want to see the colors. I
used $A$1 in this example.

Select your range (say A1:X99)
Then Format|conditional formatting
Formula is: =AND($A$1="show",CELL("protect",A1))

and give it a nice fill color.

Put Show in A1 and you'll see the locked cells. Change A1 and it's back to
normal.


Shrikant wrote:
>
> I want to shade / color locked cells to distinguish them from non-locked
> cells. However I do not wish these shading or colors to be printed. It should
> only for viewing on the screen. How to do it ?
> --
> Shrikant

--

Dave Peterson

Select your range (say A1:X99)
Then Format|conditional formatting
Formula is: =cell("protect",a1)

and give it a nice fill color.

You could even use a cell that would allow you to show it or hide that CF
formatting.

I used $A$1 in this formula:

=AND($A$1="show",CELL("protect",A1))

Put Show in A1 and you'll see the locked cells. Change A1 and it's back to
normal.

[Shrikant]

Thanke Dave. Yr reply answered my query.
I am at public i cafe and wanted to mail it to my inet account so that
later on I can use your reply to solve my problem. Alternatively, can I
'bookmark' this reply so that lateron I can easily return it for use. You
know this site is flooded with questions and my solitary entry will get
drowned - making it difficult to locate it later on.
This is out of the main topic. But very much relevant for me.
--
Shrikant


"Dave Peterson" wrote:

> Do you use Format|Conditional formatting?
>
> If no, then you could do this:
>
> Pick a cell that you can use to indicate that you want to see the colors. I
> used $A$1 in this example.
>
> Select your range (say A1:X99)
> Then Format|conditional formatting
> Formula is: =AND($A$1="show",CELL("protect",A1))
>
> and give it a nice fill color.
>
> Put Show in A1 and you'll see the locked cells. Change A1 and it's back to
> normal.
>
>
> Shrikant wrote:
> >
> > I want to shade / color locked cells to distinguish them from non-locked
> > cells. However I do not wish these shading or colors to be printed. It should
> > only for viewing on the screen. How to do it ?
> > --
> > Shrikant
>
> --
>
> Dave Peterson
>
> Select your range (say A1:X99)
> Then Format|conditional formatting
> Formula is: =cell("protect",a1)
>
> and give it a nice fill color.
>
> You could even use a cell that would allow you to show it or hide that CF
> formatting.
>
> I used $A$1 in this formula:
>
> =AND($A$1="show",CELL("protect",A1))
>
> Put Show in A1 and you'll see the locked cells. Change A1 and it's back to
> normal.
>

[Dave Peterson]

Since the response was pretty small, you could have written it down on a piece
of paper/napkin.

Or you can use google to search for stuff you've posted (and find the replies,
too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_gr...Excel*&num=100

Shrikant wrote:
>
> Thanke Dave. Yr reply answered my query.
> I am at public i cafe and wanted to mail it to my inet account so that
> later on I can use your reply to solve my problem. Alternatively, can I
> 'bookmark' this reply so that lateron I can easily return it for use. You
> know this site is flooded with questions and my solitary entry will get
> drowned - making it difficult to locate it later on.
> This is out of the main topic. But very much relevant for me.
> --
> Shrikant
>
> "Dave Peterson" wrote:
>
> > Do you use Format|Conditional formatting?
> >
> > If no, then you could do this:
> >
> > Pick a cell that you can use to indicate that you want to see the colors. I
> > used $A$1 in this example.
> >
> > Select your range (say A1:X99)
> > Then Format|conditional formatting
> > Formula is: =AND($A$1="show",CELL("protect",A1))
> >
> > and give it a nice fill color.
> >
> > Put Show in A1 and you'll see the locked cells. Change A1 and it's back to
> > normal.
> >
> >
> > Shrikant wrote:
> > >
> > > I want to shade / color locked cells to distinguish them from non-locked
> > > cells. However I do not wish these shading or colors to be printed. It should
> > > only for viewing on the screen. How to do it ?
> > > --
> > > Shrikant
> >
> > --
> >
> > Dave Peterson
> >
> > Select your range (say A1:X99)
> > Then Format|conditional formatting
> > Formula is: =cell("protect",a1)
> >
> > and give it a nice fill color.
> >
> > You could even use a cell that would allow you to show it or hide that CF
> > formatting.
> >
> > I used $A$1 in this formula:
> >
> > =AND($A$1="show",CELL("protect",A1))
> >
> > Put Show in A1 and you'll see the locked cells. Change A1 and it's back to
> > normal.
> >

--

Dave Peterson

[GEOFF]

--
GNL


"Shrikant" wrote:

> I want to shade / color locked cells to distinguish them from non-locked
> cells. However I do not wish these shading or colors to be printed. It should
> only for viewing on the screen. How to do it ?
> --
> Shrikant

[Gord Dibben]

Here is macro.

Sub Locked_Cells()
'Bob Flanagan source of code
Dim cell As Range, tempR As Range, rangeToCheck As Range
'check each cell in the selection
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell.Locked Then
If tempR Is Nothing Then
'initialize tempR with the first qualifying cell
Set tempR = cell
Else
'add additional cells to tempR
Set tempR = Union(tempR, cell)
End If
End If
Next cell
'display message and stop if no cells found
If tempR Is Nothing Then
MsgBox "There are no Locked cells " & _
"in the selected range."
End
End If
'select qualifying cells
tempR.Interior.ColorIndex = 3 'red
'change to =xlnone for printing
End Sub

When you go to print, choose "Black and White" or make the change to the macro
and re-run it.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo


Gord Dibben MS Excel MVP


On Tue, 20 Jun 2006 14:08:02 -0700, GEOFF <PERSON@EXAMPLE.COM (DONOTSPAM)>
wrote:

>
>--
>GNL
>
>
>"Shrikant" wrote:
>
>> I want to shade / color locked cells to distinguish them from non-locked
>> cells. However I do not wish these shading or colors to be printed. It should
>> only for viewing on the screen. How to do it ?
>> --
>> Shrikant

Gord Dibben MS Excel MVP