How to Lock Excel Worksheet Cells with Content

This tip is an excerpt from Excel Tips where you can find the comprehensive explanation on how to unlock empty cells (or lock filled-in cells) either manually or using a macro.

Sub UnlockEmptyCells()


   Dim myCell As Range
   Set myCell = Selection
   Cells.Select
   Selection.Locked = True
   myCell.Select
   Selection.SpecialCells(xlCellTypeBlanks).Select
   Selection.Locked = False
   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   myCell.Select

End Sub

Comments:

• Running this macro will leave all cells inside the input area unlocked if they are empty. All cells outside the input area will be locked including the empty ones. For Example, if the most bottom right cell with input is G20 all the columns from H and the rows from 21 will be locked.
• If you need a wider range just add spaces to the most bottom right cell first thing in the macro to let it be the endpoint cell.
• The macro won't run twice in a row. You need to unprotect the sheet before you run it next time. You can add the appropriate 'unprotect' code to the macro to avoid manual coding or record such a mcro and use two buttons, one to protect and the other to unprotect.
• This tip applies to 2003, 2007 and 2010.