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 (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Nick" wrote in message
...
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