Shading Locked Cells

G

Guest

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 ?
 
G

Guest

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

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.

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 ?

--

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.
 
G

Guest

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.
 
G

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



Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top