Problems with sheet unprotect

[Wayne]

Hi all,

I'm having trouble with Excel sheet protection. I'm programming a macro that
will run through an Excel Workbook, searching a every sheet for certain text
and then shading the first 31 columns of the row in which the text is found.
The sheet is protected, but the code (below) tries to unprotect before it
runs. The unprotect method seems to succeed (no errors are countered), but
when it tries to change the ColorIndex property it fails as the sheet is
still protected.

In this code, each sheet is named as the date in the format "mmm yyyy", and
the code only runs on the sheet that is equal to the variable StartMonth.
I've commented in the code below where the error is encountered.

Any help would be greatly appreciated.

Thanks,
Wayne

--------------------------------------------
For C = 1 To Sheets.Count
Sheets(C).Activate
ActiveSheet.Unprotect ("PASSWORD")
For D = 1 To 74
' Only checks the first 74 rows after Range("A4")
If Range("A4").Offset(D, 0).Value = "TEXTTOCHECKFOR" Then
For E = 1 To 31
' Shade and enter 'X' in the first 31 columns
Range("A4").Offset(D, E).Interior.ColorIndex =
12632256
' THE LINE ABOVE IS WHERE IT CRASHES
Range("A4").Offset(D, E).Value = "X"
Next E
Exit For
' Stop checking for "TEXTTOCHECKFOR" once it's been found
End If
Next D
ActiveSheet.Protect ("PASSWORD")
Next C

 

[Nigel]

Hi
Your problem is not with sheet protect but the value of the ColorIndex, this
refers to the palette color which has only 56 values. Values above 56 will
throw an error.

Range("A4").Offset(D, E).Interior.ColorIndex = 5 'e.g. - set it blue


If you want to specify a specific color you could use the RGB function,
values for each color RGB can be 0 to 255 e.g.

Range("A4").Offset(D, E).Interior.Color = RGB(0, 0, 255) ' e.g. set it
blue