Format cell on change and reset format on previous cell

[bs0d]

Heres what I would like to do:

Anytime a user clicks within specified cell ranges:

Format current cell to draw focus.

Then when another cell is activated (or at focus):

The previous cell is returned to its former format, and new format applied to current cell.


So I need find out how to store the current cells format, replace it with new format (like a yellow background). And when a new cell is selected, reapply original format to previous selected well, and repeat for newly selected cell.

Any thoughts on this? I was able to color the current cell easy enough, but having troubles even detecting previous cell properly.

 

[bs0d]

I found this similar thread: http://www.mrexcel.com/forum/showthread.php?t=16684&page=2

And I have something working where you click a cell and the background color is set (to yellow), and the previous active cell format is cleared - so only the active cell has a yellow highlight.

The remaining problem is that errors are produced when you protect the sheet, and only want this to apply to unlocked cells.

Also, on the "previous active cell"- it would be nice to restore the original format of the cell, instead of just clearing it.

Thanks

 

[bs0d]

Ok I'm progressing through this.

I added a condition to only apply the formatting if the cell is unlocked.

The latest error is that when the sheet is in protection mode, it still prevents the cell formatting eventhough its unlocked. It seems a bit backwards...


I need to protect the sheet from user-formatting for locked cells only

 

[bs0d]

Ok I've chronicled my own solution here. Best thing I found to do was unprotect sheet in code, do what I need to do, and re-protect it.

Thanks