Shading Unlocked Cells

It really used to annoy me that Excel did not highlight unlocked cells the way Lotus did. Lotus would automatically shade unlocked cells a nice green so that they were easy to identify when working in a protected worsheet.  In Excel, you don't get that. If you press the TAB key, it will take you from unlocked cell to unlocked cell; however, sometimes it is nice to visually see the layout so you can make sure you know where to enter the data and what is going on.

So, if you use protected worksheets and want users to see the unlocked cells, before you protect the sheet, do the following:

• Select the entire workbook
• Click on Conditional Formatting
• Select New Rule
• Click on Use a formula to determine which cells to format
• Type =Cell("protect",A1)=0
• Select Format
• Select Fill
• Select a light color
• Click OK
• Click OK
  

Any cells that have been 'unlocked' will be shaded.
The 0 denotes unlocked cells. If you change the 0 to a 1 it would show locked cells.