ColorIndex

[Trevor Davidson]

The Code below works just fine when the Worksheet is
unprotected.
The problem starts when I protect the worksheet. All
cells in the code are unprotected.
I get an error 1004.

Please Help

Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("C23") < 8 Then
Range("C8:C22").Interior.ColorIndex = 6
Else
Range("C8:C22").Interior.ColorIndex = 44
End If
End Sub

 

[Ron de Bruin]

Hi Trevor

In Excel 2002/2003 you can protect you sheet with
"Allow Format Cells" checked

You code is working then


If you use 97/2000
You must protect your sheet with code like this

Protect your worksheets with code with userfaceonly like this
Place this in the Thisworkbook module.

The macro's will be working now
It will only protect the userfaceonly

Right click on the Excel icon next to File in the menubar
And choose View code

You are now in the Thisworkbook module
Paste the Event in this place
Alt-Q to go back to Excel

Private Sub Workbook_Open()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect "ABCD", , , userinterfaceonly:=True
Next sh
Application.ScreenUpdating = True
End Sub

 

[Frank Kabel]

Hi Trevor
which Excel version are you using?.
In later versions you can enable formating cells

 

[Guest]

Wrap it in Unprotect, Protec

Sub Worksheet_SelectionChange(ByVal target As Range) If Range("C23") < 8 The
ActiveWorkbook.UnProtect Password := "Your password here ' if no pass word, just use ActiveWorkbook.UnProtec

Range("C8:C22").Interior.ColorIndex =
Els
Range("C8:C22").Interior.ColorIndex = 4
End I
ActiveWorkbook.Protect Password := "Your password here

End Su


----- Trevor Davidson wrote: ----

The Code below works just fine when the Worksheet is
unprotected
The problem starts when I protect the worksheet. All
cells in the code are unprotected
I get an error 1004

Please Hel

Sub Worksheet_SelectionChange(ByVal target As Range
If Range("C23") < 8 The
Range("C8:C22").Interior.ColorIndex =
Els
Range("C8:C22").Interior.ColorIndex = 4
End I
End Su

 

[Guest]

Microsoft 2000

-----Original Message-----
Hi Trevor
which Excel version are you using?.
In later versions you can enable formating cells

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Guest]

Can you use ActiveWorkbook.unprotect under Sub Worksheet?

-----Original Message-----
Wrap it in Unprotect, Protect

Sub Worksheet_SelectionChange(ByVal target As Range) If Range("C23") < 8 Then
ActiveWorkbook.UnProtect Password := "Your password

here ' if no pass word, just use ActiveWorkbook.UnProtect