Macro doesn't work when sheet protected

[Guest]

This is the Macro I have in my work sheet. Even when I set G3:F3 as
un-locked the Macro ceases to reset the color in respect to the cell
contents. When I un-protect the sheet it does exactly what it is supposed
to. Any suggestions on what to add to the code and where to put it in? (I'm
a complete newbie so I really am only cutting and pasting at this point.)
Any help would be greatly appreciated.
Thanks in advance!

Excel-chump

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "g3:f38" '<=== change to suit


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "YES": .Interior.ColorIndex = 4 'green
Case "NO": .Interior.ColorIndex = 3 'red
Case "A1": .Interior.ColorIndex = 12 'dark yellow
Case "A2": .Interior.ColorIndex = 6 'yellow
Case 0: .Interior.ColorIndex = 19 'blank

End Select
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub

'0 blank
'1 black
'2 white
'3 red
'4 green
'5 blue
'6 yellow
'7 magenta
'8 cyan
'9 maroon
'10 dark green
'11 dk blue
'12 dk yellow
'13 dk magenta
'14 dk cyan
'15 grey
'16 dk grey
'17 lt purple


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[eliano]

Hi Excel-chump.
Try to unprotect the sheet at the beginning of the routine and protect
again at the end
That is:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Activesheet.Unprotect
.............
.......your code
..........
Activesheet.Protect

Regards
Eliano

 

[Guest]

Thank you very much Eliano! It works perfectly! I really do feel like a
chump for it being so simple though. <g>
Excel-chump