jimpickles
New Member
- Joined
- Aug 15, 2011
- Messages
- 6
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?
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?