Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Shading Locked Cells
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shading Locked Cells
-- 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shading Locked Cells
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 InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. 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 (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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
All Cells in Excel are locked, what's wrong? | Excel Discussion (Misc queries) | |||
requires that merged cells must be identically sized? | Excel Discussion (Misc queries) | |||
Shading Cells | Excel Worksheet Functions | |||
When shading cells using the 'pattern' option, they print in grey. | Excel Discussion (Misc queries) | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |