Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cell Protection Color
I have a large spreadsheet that has alot of single cells that need to be
locked. Is there an easy way to see the locked/unlocked status of the cells on a spreadsheet? Maybe by having locked cells shaded? Thank you for any help anyone can give. |
#2
|
|||
|
|||
Are you using Format|conditional Formatting?
If no, you could use that to shade the locked cells. Select your range (assuming A1 is the activecell in that selection) format|conditional formatting formula is: =CELL("protect",A1) Format to your liking. ====== I use this: Tools|customize|commands tab|Format category Near the bottom of that list of icons/commands is: Lock Cell. (drag it to your favorite toolbar) It's useful for toggling the lockedness of a cell. But you can select a cell, look at that icon. If it's depressed, the cell is locked. If it's not depressed (happy as a clam???), then the cell is unlocked. Kmagg wrote: I have a large spreadsheet that has alot of single cells that need to be locked. Is there an easy way to see the locked/unlocked status of the cells on a spreadsheet? Maybe by having locked cells shaded? Thank you for any help anyone can give. -- Dave Peterson |
#3
|
|||
|
|||
Kmagg
Dave's CF is a simple method and works well, but uses up one of the CF conditions for those cells. You could also use a macro to color the locked cells. Sub Locked_Cells() Dim Cell As Range, tempR As Range, rangeToCheck As Range For Each Cell In Intersect(Selection, ActiveSheet.UsedRange) If Cell.Locked Then If tempR Is Nothing Then Set tempR = Cell Else Set tempR = Union(tempR, Cell) End If End If Next Cell If tempR Is Nothing Then MsgBox "There are no Locked cells " & _ "in the selected range." End End If tempR.Select tempR.Interior.ColorIndex = 3 'which is red End Sub Gord Dibben Excel MVP On Wed, 09 Feb 2005 18:58:10 -0600, Dave Peterson wrote: Are you using Format|conditional Formatting? If no, you could use that to shade the locked cells. Select your range (assuming A1 is the activecell in that selection) format|conditional formatting formula is: =CELL("protect",A1) Format to your liking. ====== I use this: Tools|customize|commands tab|Format category Near the bottom of that list of icons/commands is: Lock Cell. (drag it to your favorite toolbar) It's useful for toggling the lockedness of a cell. But you can select a cell, look at that icon. If it's depressed, the cell is locked. If it's not depressed (happy as a clam???), then the cell is unlocked. Kmagg wrote: I have a large spreadsheet that has alot of single cells that need to be locked. Is there an easy way to see the locked/unlocked status of the cells on a spreadsheet? Maybe by having locked cells shaded? Thank you for any help anyone can give. |
#4
|
|||
|
|||
COOL!!! Works perfectly. I just went into the Edit Macro, posted your code
and all the locked cells turned red. Thanks so much for the excellent advice. Kerry "Gord Dibben" wrote: Kmagg Dave's CF is a simple method and works well, but uses up one of the CF conditions for those cells. You could also use a macro to color the locked cells. Sub Locked_Cells() Dim Cell As Range, tempR As Range, rangeToCheck As Range For Each Cell In Intersect(Selection, ActiveSheet.UsedRange) If Cell.Locked Then If tempR Is Nothing Then Set tempR = Cell Else Set tempR = Union(tempR, Cell) End If End If Next Cell If tempR Is Nothing Then MsgBox "There are no Locked cells " & _ "in the selected range." End End If tempR.Select tempR.Interior.ColorIndex = 3 'which is red End Sub Gord Dibben Excel MVP On Wed, 09 Feb 2005 18:58:10 -0600, Dave Peterson wrote: Are you using Format|conditional Formatting? If no, you could use that to shade the locked cells. Select your range (assuming A1 is the activecell in that selection) format|conditional formatting formula is: =CELL("protect",A1) Format to your liking. ====== I use this: Tools|customize|commands tab|Format category Near the bottom of that list of icons/commands is: Lock Cell. (drag it to your favorite toolbar) It's useful for toggling the lockedness of a cell. But you can select a cell, look at that icon. If it's depressed, the cell is locked. If it's not depressed (happy as a clam???), then the cell is unlocked. Kmagg wrote: I have a large spreadsheet that has alot of single cells that need to be locked. Is there an easy way to see the locked/unlocked status of the cells on a spreadsheet? Maybe by having locked cells shaded? Thank you for any help anyone can give. |
#5
|
|||
|
|||
Thanks for the feedback.
Glad to help. Gord On Thu, 10 Feb 2005 06:25:04 -0800, "Kmagg" wrote: COOL!!! Works perfectly. I just went into the Edit Macro, posted your code and all the locked cells turned red. Thanks so much for the excellent advice. Kerry "Gord Dibben" wrote: Kmagg Dave's CF is a simple method and works well, but uses up one of the CF conditions for those cells. You could also use a macro to color the locked cells. Sub Locked_Cells() Dim Cell As Range, tempR As Range, rangeToCheck As Range For Each Cell In Intersect(Selection, ActiveSheet.UsedRange) If Cell.Locked Then If tempR Is Nothing Then Set tempR = Cell Else Set tempR = Union(tempR, Cell) End If End If Next Cell If tempR Is Nothing Then MsgBox "There are no Locked cells " & _ "in the selected range." End End If tempR.Select tempR.Interior.ColorIndex = 3 'which is red End Sub Gord Dibben Excel MVP On Wed, 09 Feb 2005 18:58:10 -0600, Dave Peterson wrote: Are you using Format|conditional Formatting? If no, you could use that to shade the locked cells. Select your range (assuming A1 is the activecell in that selection) format|conditional formatting formula is: =CELL("protect",A1) Format to your liking. ====== I use this: Tools|customize|commands tab|Format category Near the bottom of that list of icons/commands is: Lock Cell. (drag it to your favorite toolbar) It's useful for toggling the lockedness of a cell. But you can select a cell, look at that icon. If it's depressed, the cell is locked. If it's not depressed (happy as a clam???), then the cell is unlocked. Kmagg wrote: I have a large spreadsheet that has alot of single cells that need to be locked. Is there an easy way to see the locked/unlocked status of the cells on a spreadsheet? Maybe by having locked cells shaded? Thank you for any help anyone can give. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing value of a cell based on another cell color | Excel Discussion (Misc queries) | |||
Change cell back color on click | Excel Discussion (Misc queries) | |||
Current Cell Color | Excel Worksheet Functions | |||
cell color shading | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |