Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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) enter image description here

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.