Code to change the color of "Rectangle21_Click"

[Ed]

I need the option to change the background color of a cell
to as many as 5 different colors and still be able to
protect the cell. I can accomplish 4 different colors with
"Conditional Formatting".
I could use a "Rectangle" to cover the cell, and then
switch the color of the rectangle from a color to
transparent to get the fifth color.
Is there Code that will change the color of a "Rectangle"?
Such as: Worksheets("abc").?????????
Or is that impossible?
Thankyou,
Ed

 

[Tom Ogilvy]

Why not skip the rectangle and use the Change event or the calculate event
to change the background color of a cell since you are exceeding the
capabilities of conditional formatting.

You can unprotect the sheet in your code and change the background color,
then reprotect the sheet. Alternatively you can use the UserInterActiveOnly
property of the protect method to allow you macro to work on a protected
sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
me.Protect UserInterfaceOnly:=True
if Target.Address = "$B$9" then
select Case Target.Value
case 1
target.Interior.colorIndex = 3
case 2
target.interior.colorIndex = 4
End Select
End if
End Sub

Obviously, the case statement would need to be adjusted to accomplish the
functionality you want. In xl2003 and perhaps xl2002 you need to supply
the password with the protect command (if the sheet has one).

 

[Guest]

-----Original Message-----
Why not skip the rectangle and use the Change event or the calculate event
to change the background color of a cell since you are exceeding the
capabilities of conditional formatting.

You can unprotect the sheet in your code and change the background color,
then reprotect the sheet. Alternatively you can use the UserInterActiveOnly
property of the protect method to allow you macro to work on a protected
sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
me.Protect UserInterfaceOnly:=True
if Target.Address = "$B$9" then
select Case Target.Value
case 1
target.Interior.colorIndex = 3
case 2
target.interior.colorIndex = 4
End Select
End if
End Sub

Obviously, the case statement would need to be adjusted to accomplish the
functionality you want. In xl2003 and perhaps xl2002 you need to supply
the password with the protect command (if the sheet has one).
--
Regards,
Tom Ogilvy





.
Hi Tom

I used the following code at the beginning of the Macro:
Worksheets("abc").Unprotect
And this at the end:
Worksheets("abc").Protect

It works fine except for one thing.

If you protect the sheet with a password, you have to enter
the password in order to run the Macro.
You would have to give the password to who ever was going
to run the Macro. Making it impossible to protect the sheet.
Do you know of a remidy for this?
Thankyou
Ed