Run-time error '1004' when Protecting Sheet

[Aaron DOJ]

Hello Excel champions of the world!

I am having a confusing problem...
All my code seems to work just fine until I protect the worksheet, I get run-time error '1004': application-defined or object-defined error. The code underlined is where the first hit comes into play and I am not understanding why. If the code works when not protected, why is it yelling at me when protected? My cells are merged, not sure if that is the issue...I understand that merged cells create a lot of hassle, but I need them.

Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Cells(19, 3)) Then 'if empty, put in "Value" and make value change text to teal.
    Cells(19, 3).Value = "123456"
    Cells(19, 3).Font.ColorIndex = 14
ElseIf Sheet1.Cells(19, 3).Value = "123456" Then 'if equal to "Value", change text to teal.
    [U]Cells(19, 3).Font.ColorIndex = 14[/U]
ElseIf Sheet1.Cells(19, 3).Value <> "123456" Then 'if text other than "Value", change text to black.
    Cells(19, 3).Font.ColorIndex = 1
End If


End Sub

 

[steve the fish]

Think you answered your own question. Try changing the font colour manually when the sheet is protected. You cant. For that same reason you cant in code. You need to unprotect the sheet first then protect it again at the end of the code.

 

[Aaron DOJ]

Thank you Steve, that seems right, but I have formatted those cells to allow users to edit them...or so I thought lol. Maybe I am not understanding the ins and outs of it all, but let me explain what I think I am doing and tell me where my thinking is flawed if you will.
Worksheet is unprotected, I have selected the hole sheet using the top left grey box between row 1 and column A, then I right clicked, went to format cells, Protection tab checked locked and hidden.
I then went back to the sheet and selected what I want the users to be able to change and select (which includes the cell above) followed the format cell steps as above un-checking locked.
Then Review Tab, Protect Sheet and selected unlocked cells....I just realized I needed to have format cells checked too....now it all works and no errors and coding works...hey its monday

Thanks for your time Steve!!