Macro doesn't work when sheet protected


G

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.
 
Ad

Advertisements

E

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
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top