To highlight unprotected cells (cells that are unlocked) with conditional formatting, you can use a formula based on the CELL function. In the example shown, the custom formula used to set up conditional formatting is:
Where A1 represents the active cell in the selection when the rule was defined.
How this formula works
The CELL function can provide a wide range of information about cell properties. One property is called "protect" and indicates whether a cell is unlocked or locked. All cells start out "locked" in a new Excel workbook, but this setting has no effect until a worksheet is protected.
The CELL function returns either 1 or zero to indicate "on" or "off". In this case we are comparing the result to zero, so when CELL returns 0, the expression returns TRUE and the conditional formatting is triggered. When CELL returns 1, the expression returns FALSE and no conditional formatting is applied.
The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the...
Conditional formatting with formulas can be tricky because you can't see what happens to the formula when the rule is applied. Dummy formulas let you visualize how formulas will behave before you create a rule. This video shows you how to test...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.