How to make many protection properties on Ranges when I protect my Sheet?

[jiura]

Hello to all!
I write a program on Excel, but I have a such problem:
I want to protect my sheet. But I also want that user could:
a) Change Everything (Formulas, back colors and font colors e.t.c. ) in one Range of Cells
b) Change Nothing in Second Range of cells (only watch)
c) Change formulas and Font colors in Third range of cells.

But when I set a protection on the sheet, I can make only two types of cells in this sheet: one type of cells are non protected and I can do everything with them. The second types are protected, and I can do only things that I allow at protection. But I also want to have the third, fourth and five Ranges with other protection properties. For Example at third Range user could change only font color, font names, and Back color. At forth Range user could change only formulas e.t.c. How can I do that?

[Jan Karel Pieterse]

You can only (maybe!) enforce that using VBA and a set of worksheet event routines. I am not sure this is very feasible though.

[mikerickson]

This scenario might work. User opens sheet, enters a password which unlocks the cells appropriate to their userStatus.

Another scenario would be to take over all of the formatting functions. Like this MyCellColor example (in pseudo-code).

Pseudo Code

Sub MyColor()

If Appliation.Intersect(Selection,noColorChangeRange) Is Nothing Then
Exit Sub
Else
ActiveSheet.Unprotect
Call changeTheColor(Selection)
ActiveSheet.Protect
End If

End SubYou'd then have to replace the normal controls with ones pointing to your routine. (And put them back when you are done.)

I suspect there is an easier way to acheive your goal.