The default setting in Excel is for all cells to be locked. If you want users to input in a cell you must unlock that cell before you apply sheet protection. You can use a formula to identify locked and unlocked cells.
Cell locking and unlocking is done via the Format Cells dialog – see image below. Cell A1 is unlocked.
The CELL function has an option to return 0 if a cell is unlocked and 1 if a cell is locked – see image below. The formula in cell B1 has been copied down.
The yellow cells are unlocked and cell A4 is locked.
Custom Function
We can create a simple custom function to determine if a cell is locked.
Cell C1 (image below) has the LAMBDA function required to test the custom function. This formula returns TRUE if the cell is locked and FALSE if it is unlocked.
The formula in cell C1 is.
=LAMBDA(ref,CELL("protect",ref)=1)(A1)
The Range Name definition for the custom function is shown below.
The custom function in use is shown below.
Check out this post which discusses putting Excel into lock down.
Interesting idea… let’s extend it so that fnISLOCKED will handle a single cell or a range of cells. Use this LAMBDA in place of the one you posted…
=LAMBDA(ref,MAKEARRAY(ROWS(ref),COLUMNS(ref),LAMBDA(r,c,0=CELL(“protect”,OFFSET(ref,r-1,c-1)))))(A1:C6)
Thanks very much Rick – That MAKEARRAY structure may offer solutions to convert other custom functions to handle a range.