Format Change Event for Excel VBA

Question

I'm trying to prevent people from modifying (most) of my spreadsheet while still alowing them to use the sort function of the AutoFilter. What I've done so far is used the Worksheet_Change event and Application.Undo to automatically undo any values entered into cells that I don't want to be modified. This works great, except that it can't detect changes in formatting. Does anyone know of a means to trigger an event when the format (text/background color) of a cell is changed?

Thanks!

Answer 1

The events you are limited to at the worksheet level are as follows:

Activate
BeforeDoubleClick
BeforeRightClick
Calculate
Change
Deactivate
FollowHyperLink
PivotTableUpdate
SelectionChange

I suppose you could try something at the SelectionChange event. If someone changes the formatting of a cell and then moves to a different cell that event will be called.

Whatever code you have in that area will constantly be called, so it would be a good idea to preface whatever you do with some quick checks to make sure the cells are in a certain range, have data, etc. Your sheet could get pretty sluggish otherwise as users move from cell to cell.