Strange but true. Excel doesn't allow formatting unprotected cells on a
protected sheet.

So the approach would still apply.

In a module, put the Auto_Open code. Adjust the "Whatever_Sheet" to suit
your workbook.
Close the workbook, reopen, then try the double click again.

Userinterfaceonly = true allows macros to make changes to protected sheets.
It remains protected against changes through the UI though. (strange
terminology, "userinterfaceonly = true" belies the actual behavior). It
executes only once after the workbook is opened and then the macro can make
changes until the workbook is closed.

Another way is to unprotect every time you want to change, then reprotect
when done:

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D128")) _
Is Nothing Then
Target.Parent.Unprotect 'Password:="whatever"
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
Target.Parent.Protect 'Password:="whatever"
End If
End Sub

The second approach works similarly well, with some drawbacks. If the macro
crashes while unprotected the sheet is vulnerable. There might be a lot of
extra code to add if the project is big.

Userinterfaceonly has drawbacks too. Other macros outside the workbook can
make changes to the protected sheet. Most, but not all, actions can be done
against a protected sheet. There is a documented bug w/respect to
FillAcrossSheets if I recall correctly.


"gav meredith" wrote in message
...
Hi Tim,

The cells im looking to alter arent protected but the sheet is. Is this
the
same as you suggested. I am sorry but could you explain a little more as
to
how to implement this....im a complete novice!!!

Thanks!!!

"Tim Zych" wrote in message
...
The macro is trying to change a protected cell.

In the workbook's open event reprotect with UserInterfaceonly to True.
It
will let macros make changes to the protected sheet.

Sub Auto_Open()
Whatever_Sheet.Protect UserInterFaceOnly:=True, _
Password:="whatever"
End Sub


"gavin" wrote in message
...
Hello all,

a code i use to alter the colour of a cell when double clicked, will
not
run when i protect the sheet??

Can someone shed some light on what im sure is a simple fix i have
missed
completely??

Cheers!!!!!

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D128")) _
Is Nothing Then
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
End If
End Sub