How to apply a conditional formatting rule to a complete workbook and/or worksheet

[hershmab]

I have created a multi-sheet workbook for a friend. Occasionally he has entered data into cells with formulas and thus destroyed the logic. I want to show him the error of his ways by formatting all cells with formulas in a distinctive way.

I have already provided his workbook with a UDF that returns TRUE if the cell it is applied to has a formula. I have tested the use of this UDF in a conditional formatting(UK spelling!) rule applied to specifically-selected cells.

It does work for me, BUT:

1. I am developing the application in Excel 2010, but it has to run in 2007 - will 2010 CF rules work the same in both?
2. When data rows/columns are added to a sheet, the rule no longer applies globally - how can I select the whole worksheet even if its size and contents change?
3. How can I copy such a rule from one worksheet to the whole workbook?

While I am eager to get answers to these question, I am also open to suggestions of doing it a simpler way.

 

[AlphaFrog]

An alternative could be to unlock all cells except the formula cells and password protect the sheet(s) so the user cannot select or change the locked formula cells.

 

[hershmab]

An alternative could be to unlock all cells except the formula cells and password protect the sheet(s) so the user cannot select or change the locked formula cells.

Definitely a simpler and probably more effective way of preventing the user from overwriting formulas!

A further problem presents itself:

Many of the worksheets are formatted as tables so that when rows are added they automatically inherit the formulas and formats of the existing rows. Would the locked/unlocked status be inherited as well?
​

I presume that I could write a VBA one-use macro to do the (un)locking and change, if desired, the formatting of cells according to their resulting status?

 

[mfexcel]

To make the Table expandable in a protected sheet, I believe you may need to unlocked the cells beneath the Table

Many of the worksheets are formatted as tables so that when rows are added they automatically inherit the formulas and formats of the existing rows. Would the locked/unlocked status be inherited as well?
​

 

[AlphaFrog]

Would the locked/unlocked status be inherited as well?

I've never tried it.

I presume that I could write a VBA one-use macro to do the (un)locking and change, if desired, the formatting of cells according to their resulting status?

I don't know your VBA skill set, but yes an macro could do that.

Another suggestion is to use the Data Validation Input message which doesn't stop the user from changing a formula cell but rather gives them a warning. Select all the formula cells and configure a Data Valudation Input message that states They shouldn't change a formula cell or you will rain fire and hail upon them. They would see that message popup automatically when thay select a formula cell.

 

[hershmab]

My own solution (that works!) is to have an OnSelectionChange worksheet macro. If the active (selected) cell has a formula, the macro simply moves the focus to the next cell (right and/or down, as necessary) that does not have a formula.