ColorIndex

T

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
 
R

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
 
F

Frank Kabel

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

chris

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
 
G

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



.
Click to expand...
 
G

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
Click to expand...
here ' if no pass word, just use ActiveWorkbook.UnProtect
 
Top