Error on password protected sheet.

[Ram B]

hi- I am working on a sheet that has the following code. I get an error
(Run-time error '1004': Unable to set the ColorIndex property of the Interior
class) and pointing at the line marked ***(cell.Interior.ColorIndex =
icolor1)( I added the *** is not is the code). The workbook also has a code
in the module called "auto_open" that is supposed to allow users to use
outlining on the protected sheet. the coloring works fine if I remove the
auto_open sub but I need to pwd protect and allow the user to operate on the
created outlining. Any help will be appreciated.

--------In worksheet I have---------
Private Sub Worksheet_Calculate()
Dim icolor1 As Long
Dim icolor2 As Long
Dim cell As Range

For Each cell In Range("F12:M400")

icolor1 = xlColorIndexNone
icolor2 = xlColorIndexAutomatic
Select Case cell.Value
Case "Red": icolor1 = 3: icolor2 = 3
Case "Green": icolor1 = 4: icolor2 = 4
Case "Blue": icolor1 = 5: icolor2 = 5
Case "White": icolor1 = 2: icolor2 = 2
Case "Gray": icolor1 = 15: icolor2 = 15
Case "x": icolor1 = 1: icolor2 = 1
Case "xx": icolor1 = 40: icolor2 = 40
Case "yy": icolor1 = 36: icolor2 = 36
Case "Not Assessed": icolor1 = 2: icolor2 = 40
Case "Missing Info.": icolor1 = 2: icolor2 = 3
Case Else: 'Whatever
End Select

*** cell.Interior.ColorIndex = icolor1
cell.Font.ColorIndex = icolor2
Next cell

End Sub
----------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim R As Range
If Not Intersect(Target, Range("F12:M400")) Is Nothing Then
Application.EnableEvents = False
For Each R In Target.Cells
Select Case R.Text
Case "Red": icolor = 3
Case "Green": icolor = 4
Case "Blue": icolor = 5
Case "White": icolor = 2
Case "Gray": icolor = 15
Case "x": icolor = 1
Case "xx": icolor = 40
Case "yy": icolor = 36
Case "Not Assessed": icolor = 2
Case Else: 'Whatever
End Select
R.Interior.ColorIndex = icolor
R.Font.ColorIndex = icolor

Next R
End If
EndProc:
Application.EnableEvents = True
End Sub



------------- In the module3 I have--------------------

Sub auto_open()
With Worksheets("Passive Safety")
.Protect Password:="password", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

 

[RyanH]

You will need to unprotect the sheet, run your code, and then protect the
sheet again. If you have a password enter it in betweent he "" next to
Password.

Option Explicit

Private Sub Worksheet_Calculate()

Dim icolor1 As Long
Dim icolor2 As Long
Dim cell As Range

ActiveSheet.Unprotect Password:=""

For Each cell In Range("F12:M400")

icolor1 = xlColorIndexNone
icolor2 = xlColorIndexAutomatic
Select Case cell.Value
Case "Red": icolor1 = 3: icolor2 = 3
Case "Green": icolor1 = 4: icolor2 = 4
Case "Blue": icolor1 = 5: icolor2 = 5
Case "White": icolor1 = 2: icolor2 = 2
Case "Gray": icolor1 = 15: icolor2 = 15
Case "x": icolor1 = 1: icolor2 = 1
Case "xx": icolor1 = 40: icolor2 = 40
Case "yy": icolor1 = 36: icolor2 = 36
Case "Not Assessed": icolor1 = 2: icolor2 = 40
Case "Missing Info.": icolor1 = 2: icolor2 = 3
Case Else: 'Whatever
End Select

cell.Interior.ColorIndex = icolor1
cell.Font.ColorIndex = icolor2
Next cell

ActiveSheet.Protect Password:=""

End Sub

Hope this helps!

 

[r]

You will need to unprotect the sheet, run your code, and then protect the
sheet again. If you have a password enter it in betweent he "" next to
Password.

Option Explicit

Private Sub Worksheet_Calculate()

Dim icolor1 As Long
Dim icolor2 As Long
Dim cell As Range

ActiveSheet.Unprotect Password:=""

For Each cell In Range("F12:M400")

icolor1 = xlColorIndexNone
icolor2 = xlColorIndexAutomatic
Select Case cell.Value
Case "Red": icolor1 = 3: icolor2 = 3
Case "Green": icolor1 = 4: icolor2 = 4
Case "Blue": icolor1 = 5: icolor2 = 5
Case "White": icolor1 = 2: icolor2 = 2
Case "Gray": icolor1 = 15: icolor2 = 15
Case "x": icolor1 = 1: icolor2 = 1
Case "xx": icolor1 = 40: icolor2 = 40
Case "yy": icolor1 = 36: icolor2 = 36
Case "Not Assessed": icolor1 = 2: icolor2 = 40
Case "Missing Info.": icolor1 = 2: icolor2 = 3
Case Else: 'Whatever
End Select

cell.Interior.ColorIndex = icolor1
cell.Font.ColorIndex = icolor2
Next cell

ActiveSheet.Protect Password:=""

End Sub

Hope this helps!

Hello Ram B and RyanH,
open car used userinterfaceonly: = True then there is no need to unprotect
.... I tried the code and I have not found errors.
In Worksheet_Change is not necessary to disable the events, format changes
do not trigger the event change.
The only problem I can imagine is that in routine Private Sub
Worksheet_Calculate () ... Range ( "F12: M400") refers to an active sheet
that is not Worksheets ( "Passive Safety") ... then try to replace Range (
"F12: M400) with me.Range (" F12: M400)
goodbye
r