-----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


"Ed" wrote in message
...
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



.
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