Is it possible to Protect all Cells that are, say, Colored Gray?

[Nick]

Hi Programmers,
I've realized that I need to use data validation to protect formulas
in my worksheets. However, due to the enormity of the workbook, this
would take about the rest of the summer. Also, I don't like to use
the basic cell protection feature because of Excel's pain-in-the-rear
protection popups. I am using Excel 2002. A thought occurred to me--
I've colored all cells that users should not alter with the color
gray. So, is there a way for code to detect this and then not allow
users to alter the contects of the gray-colored cells?
Thanks so much

 

[Rick Rothstein]

Give this VB idea a try. Right click the tab at the bottom of your worksheet
and select View Code from the popup window, then copy/paste the following
code into the code window that opened up, then go back to the worksheet and
try to change the contents of one or your gray cells. Be sure to read the
notes listed after the code.

'*************** START OF CODE ***************
Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Interior.ColorIndex = 15 Then
On Error GoTo Whoops
Application.EnableEvents = False
' MsgBox "Gray cells cannot be changed!"
Target.Formula = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.ColorIndex = 15 Then OldValue = Target.Formula
End Sub
'*************** END OF CODE ***************

Note 1: I assumed your gray color was the one with a ColorIndex of 15
(adjust the value in the If..Then statement accordingly).

Note 2: I included (but commented out) a MessageBox that could be displayed
to the user if you want.

Note 3: Be aware that a user can still change a cell by changing its color
from gray to any other color or to no color.

 

[Patrick Molloy]

try this

Option Explicit
Const cGREY As Long = 12632256
Sub LockSheet()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange.Cells
If cell.Interior.Color = cGREY Then
cell.Locked = False
cell.FormulaHidden = False
End If
Next
ActiveWorkbook.Protect Structure:=True, Windows:=False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

 

[Nick]

Thanks Guys,
I will try these solutions by tomorrow...
If this works, I'll need to do the same with another color, light
orange, I think color index 40.

 

[Rick Rothstein]

I'm thinking this version of my code would be safer... it prevents the user
from being able to select a range that includes a gray cell (which, if they
could do, would allow them to delete the contents of every selected cell
even if it were gray... this code prevents that)...

'*************** START OF CODE ***************
Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Interior.ColorIndex = 15 Then
On Error GoTo Whoops
Application.EnableEvents = False
' MsgBox "Gray cells cannot be changed!"
Target.Formula = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim C As Range
For Each C In Target
If C.Interior.ColorIndex = 15 Then
OldValue = C.Formula
C.Select
Exit For
End If
Next
End Sub
'*************** END OF CODE ***************

 

[Rick Rothstein]

Last change... I promise! I didn't like which cell became active when you
tried to select a range that contained a gray cell, so I now reselect the
cell the user was at prior to trying to select the range with a gray cell in
it.

'*************** START OF CODE ***************
Dim OldValue As Variant
Dim LastCell As Range

Private Sub Worksheet_Activate()
Set LastCell = ActiveCell
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Interior.ColorIndex = 15 Then
On Error GoTo Whoops
Application.EnableEvents = False
' MsgBox "Gray cells cannot be changed!"
Target.Formula = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim C As Range
For Each C In Target
If C.Interior.ColorIndex = 15 Then
OldValue = C.Formula
LastCell.Select
Exit For
End If
Next
Set LastCell = ActiveCell
End Sub
'*************** END OF CODE ***************

 

[Nick]

Hi Again,

OK, I've tried Rick's, but couldn't get it to fire. I haven't tried
Patrick's yet, but will.

I initially wondered if I had the right color, which is Gray-25%. I
then went to http://www.mvps.org/dmcritchie/excel/colors.htm. This
webpage states, "Of the descriptive color names only those for index
numbers 1 - 8 can be used in coding." Therefore, it appears that the
long color name, 12632256, will need to be used.

Nick

 

[Rick Rothstein]

First off, I just realized that the last change I made doesn't require all
the code I posted. This is all that is needed... it will prevent the user
from being able to select a gray cell at all...

'*************** START OF CODE ***************
Dim LastCell As Range

Private Sub Worksheet_Activate()
Set LastCell = ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim C As Range
For Each C In Target
If C.Interior.ColorIndex = 15 Then
LastCell.Select
Exit For
End If
Next
Set LastCell = ActiveCell
End Sub
'*************** END OF CODE ***************

Now, as to the ColorIndex... if you don't know the ColorIndex value to use,
note the address of one of your gray cell, let's say it is E2, then go into
the VB editor and type this into the Immediate window...

? Range("E2").Interior.ColorIndex

That will tell you the color index value to use in the If..Then statement in
the code above. Alternately, if your cell is really assigned a Color value
of 12632256 (as opposed to a ColorIndex value), you could try changing the
If..Then statement as follows...

If C.Interior.Color = 12632256 Then

One of the above should make the code work for you.