Testing cell protection for condtional formatting in Excel

Question

In Excel 2010, I want to use conditional formatting (to color the cell) if the cell is protected. Can you suggest the appropriate formula? I think it is IF(CELL("PROTECT").... something, but what?

Answer 1

You need a two step process

1. Identify that the cell is locked (which is what =CELL("protect",A1) does
2. Check that the sheet is protected (as locked cells are are protected when the sheet is password protected)

Happily there is a workaround to uses VBA for (2), we can use the XLM/Range Name approach to identify if the sheet is protected (see my article here) for further info on this method

To set-up for part 2

• Goto Formula
• Insert a new name =IsShtProtect1 referring to `=GET.DOCUMENT(7)+RAND()*0 (see bottom diagram)

This range name will return 1 if the sheet is protected, 0 if it is unprotected.

You can now apply the conditional formatting

1. Select your range of interest, staring from the top left corner
2. Use a formula to select which cells to format
3. add this rule =AND(IsShtProtect=1,CELL("protect",A1)=1)

This rule is TRUE when the cell is both locked and protected (A16:I20 in the example below)