run time error 1004, macros don't work if i protect cells!?

[jimpickles]

i've built a program in excel part of which uses macros to change the colour of cells when a certian word is typed into the cell:

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("d6:m35")
For Each Cell In MyPlage

If Cell.Value = "On Entry" Then
Cell.Interior.ColorIndex = 3
Cell.Font.ColorIndex = 3
End If
If Cell.Value = "Christmas" Then
Cell.Interior.ColorIndex = 5
Cell.Font.ColorIndex = 5
End If
If Cell.Value = "Easter" Then
Cell.Interior.ColorIndex = 4
Cell.Font.ColorIndex = 4
End If
If Cell.Value = "Final" Then
Cell.Interior.ColorIndex = 6
Cell.Font.ColorIndex = 6
End If

If Cell.Value <> "On Entry" And Cell.Value <> "Christmas" And Cell.Value <> "Easter" And Cell.Value <> "Final" Then
Cell.Interior.ColorIndex = xlNone
End If

these work fine, however when i finished the program and protected the cells with formulas in, the next time i used the macros they didn't work and the following mesage apeared:

run time error 1004
unable to set the colorindex of the interior class

can anyone explain why this happens and how i can get the macros to work while still protecting the cells?

 

[kpark91]

You must unprotect the cells first and then protect the cells after your code.

Because you're changing format/value of the cell that is protected, Excel does not allow the macro to run successfully.

 

[VoG]

Try (change pw to the password)

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect Password:="pw"
Set MyPlage = Range("d6:m35")
For Each Cell In MyPlage

If Cell.Value = "On Entry" Then
Cell.Interior.ColorIndex = 3
Cell.Font.ColorIndex = 3
End If
If Cell.Value = "Christmas" Then
Cell.Interior.ColorIndex = 5
Cell.Font.ColorIndex = 5
End If
If Cell.Value = "Easter" Then
Cell.Interior.ColorIndex = 4
Cell.Font.ColorIndex = 4
End If
If Cell.Value = "Final" Then
Cell.Interior.ColorIndex = 6
Cell.Font.ColorIndex = 6
End If

If Cell.Value <> "On Entry" And Cell.Value <> "Christmas" And Cell.Value <> "Easter" And Cell.Value <> "Final" Then
Cell.Interior.ColorIndex = xlNone
End If
Me.Protect Password:="pw"
End Sub

 

[jimpickles]

@kpark91
thing is im not protecting the cells which are changing colour just other ones with formulas in.
i want the program to be protected from the user so that they don't accidently delete formulas. but they need to be able to change certain cells values and for those cells to change colour when they do.

@vog
uumm ok, what will that do (i'm not that experience with macros)

 

[kpark91]

@OP:
I'm not too sure about XL2003 as I don't have it available to me right now.
but if you're using XL2007, you can protect sheet by right-clicking on the sheet tab and choose options to allow users to 'change format and values of the cells'

I'm not too sure if this has anything to do with the original question you have but hope it has helped.

 

[jimpickles]

i tried your code voG , but it didn't seem to work.
i changed the password to pw and copied you code in
but it still doesn't change the colour of the cells even without the protecting stuff

 

[jimpickles]

.

 

[VoG]

This works for me

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("d6:m35")) Is Nothing Then Exit Sub
Me.Unprotect Password:="pw"
If Target.Value = "On Entry" Then
    Target.Interior.ColorIndex = 3
    Target.Font.ColorIndex = 3
ElseIf Target.Value = "Christmas" Then
    Target.Interior.ColorIndex = 5
    Target.Font.ColorIndex = 5
ElseIf Target.Value = "Easter" Then
    Target.Interior.ColorIndex = 4
    Target.Font.ColorIndex = 4
ElseIf Target.Value = "Final" Then
    Target.Interior.ColorIndex = 6
    Target.Font.ColorIndex = 6
Else
    Target.Interior.ColorIndex = xlNone
End If
Me.Protect Password:="pw"
End Sub

 

[jimpickles]

oh ok yeah that's working now.
thanks very much. lets me just check one thing...

 

[VoG]

Copy the whole code from here http://www.mrexcel.com/forum/showpost.php?p=2828460&postcount=8 and replace what you have now with it.