Macro does not work in Protected Worksheet

I have a macro in my worksheet that highlights any cell where a change has been made. Below is the macro:

Private Sub Worksheet_Change(ByVal Target As Range)

 

Dim ws As Worksheet, ws2 As Worksheet

Dim i As Boolean

Application.ScreenUpdating = False

 

'Create Change Log if one does not exist.

i = False

For Each ws In ThisWorkbook.Worksheets

    If ws.Name = "Change Log" Then

        i = True

        Exit For

    End If

Next ws

If Not i Then

   Set ws2 = ThisWorkbook.Worksheets.Add

   ws2.Visible = xlSheetHidden

   ws2.Name = "Change Log"

   ws2.Range("A1") = "Sheet"

   ws2.Range("B1") = "Range"

   ws2.Range("C1") = "Old Interior Color"

Else

   Set ws2 = Sheets("Change Log")

End If

 

'Store previous color data in change log for rollback.

ws2.Range("A1").Offset(ws2.UsedRange.Rows.Count, 0) = Target.Worksheet.Name

ws2.Range("B1").Offset(ws2.UsedRange.Rows.Count - 1, 0) = Target.Address

ws2.Range("C1").Offset(ws2.UsedRange.Rows.Count - 1, 0) = Target.Interior.Color

 

'Change cell color to yellow.

Target.Interior.Color = 13434879

 

Application.ScreenUpdating = True

End Sub


.....When I protect the worksheet and lock certain cells from being touched, the macro stops working and an error pops up, asking if I wish to debug. There are certain ranges in the worksheet that can be touched but even when I make a change within those ranges, the error pops up for debugging.

When I continue to debug it, the following portion of the macro is highlighted: Target.Interior.Color = 13434879

Can you please advise how to run this macro in a protected worksheet?

Also, if I only wanted to apply it to a certain range of the worksheet, how would this be done?

Thank you in advance for the advice and help!

Best,
Nathaly Renderos

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

'Change cell color to yellow.
Me.Unprotect "password"
Target.Interior.Color = 13434879
Me.Protect "password"
 
Application.ScreenUpdating = True

Regards, TMS

You can include a code to unprotect the sheet before accessing the worksheet in the code and protect it again after the action is completed like

ws2.unprotect and then ws2.protect

Hope that helps.