"Todd" wrote in message
...
Is there a way to select all cells without a formula and
unlock them? Maybe a macro? I want to protect all the
cells with formulas and leave the rest open to changes.
Right now all cells with data are locked. I have a lot
of worksheets so doing this manually will take a LONG time.
TIA
Todd
Todd,
Open the
VB editor for the workbook in question. Insert a module and
then paste the following code into the module. Then run it whenever you
need to. (May not be the most elegant way, but it worked for me). [BTW you
can also use edit, goto, special to select cells with formulas. Then right
click on the selection and make the changes. You would have to do it
separately for every sheet though]
Good luck!
Bob L.
Sub LockFormulas()
Dim mysheet As Worksheet
Dim myrange As Range
Dim mycell
For Each mysheet In ThisWorkbook.Worksheets
mysheet.Activate
Set myrange = mysheet.UsedRange
For Each mycell In myrange
Select Case mycell.Formula
Case Is = ""
mycell.Locked = False 'unlock empty cells
Case Else
'distinguish between constants and formulas
If Left(CStr(mycell.Formula), 1) = "=" Then
mycell.Locked = True
Else
mycell.Locked = False
End If
End Select
Next
mysheet.Protect 'Protect the sheet
Next
End Sub