Error when using a protected sheet

Hi @Fantana, Sean M,

Thank you for your prompt update. I have tested and reproduce the issue you're experiencing on my worksheet.

Before protecting the sheet, make sure you double-check these things:

• Formula cells in column F and H are locked in Format cells > Protection > Locked
• Other cells where users can click are unlocked.

Now you can open VBA and use this script:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice
    Dim rowRange As Range
    Dim colRange As Range
    Dim activeCell As Range
    ActiveSheet.Unprotect "123" 'replace 123 with your password or use "" if no password
    Set activeCell = Target.Cells(1, 1)
    Set rowRange = Rows(activeCell.Row)
    Set colRange = Columns(activeCell.Column)
    Cells.Interior.ColorIndex = xlNone
    rowRange.Interior.Color = RGB(248, 150, 171)
    ActiveSheet.Protect "123", Contents:=True, Scenarios:=True, UserInterfaceOnly:=False
End Sub

The difference is that using the parameter UserInterfaceOnly. This will allow VBA to modify the sheet when still preventing users from making changes.

Please feel free to reach out again if you have any trouble or need further assistance. I'm happy to help.

Hope to hear from you soon.

Hi @Fantana, Sean M,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, the "Run time error '1004'" occurs because macro is trying to modify something that's restricted by Excel sheet protection. You'll need to modify your VBA code to unprotect the sheet temporarily before the highlighting action and then re-protect it afterward.

I assume your existing row highlighting code is in the Worksheet_SelectionChange event, it will be modified like this:

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
ActiveSheet.Unprotect "yourpassword" 'replace with your password
'your highlight code here
'example: 
Target.EntireRow.Interior.Color = RGB(255,255,0)
ActiveSheet.Protect "yourpassword", _
DrawingObjects := True, Contents := True, Scenarios:= True, AllowFormattingRows:=True, _
AllowFormattingColumns:=True
End Sub

Hope this will help. Please feel free to reach out again if you need help with anything. I'm happy to assist.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.