Clear all cells that are unlocked except for one

[justundertall]

Good Day,

I have a workbook for doing up estimates based on the info that is put into the Estimate Spreadsheet.
It is currently has a Button with a Macro for Clearing all unlocked cells(yellow cells) which works great, except I have one cell E5(RedCell) which has a macro to create a new estimate number every time the workbook is opened. In order for the Macro in E5 to work it has to be unlocked and always has to have a number in the cell. So when i click on the clear cells button it clears out the E5 cell.
Is there a way to make it clear all the Cells that are unlocked except for E5?

Here is a picture of my spreadsheet then of the clear cells macro

Sub ClearUnlockedCells()
Dim WorkRange As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then Cell.Value = ""
Next Cell
End Sub

 

[Tom Urtis]

You could always take the clunky but quick way of assigning a variable to cell E5's contents, clear the cells as you are doing, and reinstate the value in cell E5.

Sub ClearUnlockedCells()
Dim WorkRange As Range, strQuoteNumber as String
strQuoteNumber = Range("E5").Value
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then Cell.Value = ""
Next Cell
Range("E5").Value = strQuoteNumber
End Sub

If anyone is wondering why I opted for the String type variable, it will work as well for numbers as for text, and in my experience I would not count on digits, and only digits, to be dutifully present in the cell. Sometimes it may, by mistake or design, include a space or an alpha character.

 

[Michael M]

Or

Sub ClearUnlockedCells()
Dim WorkRange As Range, Cell As Range
Set WorkRange = ActiveSheet.UsedRange
Cells(5, 5).Locked = True
    For Each Cell In WorkRange
        If Cell.Locked = False Then Cell.Value = ""
    Next Cell
Cells(5, 5).Locked = False
End Sub

 

[Rick Rothstein]

Or without using a loop...

Sub Test()
  Dim KeepMe As String
  KeepMe = Range("E5").Value
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  ActiveSheet.UsedRange.Replace "", "", xlWhole, , , , True, False
  Application.FindFormat.Clear
  Range("E5").Value = KeepMe
End Sub

 

[justundertall]

Or without using a loop...

Sub Test()
  Dim KeepMe As String
  KeepMe = Range("E5").Value
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  ActiveSheet.UsedRange.Replace "", "", xlWhole, , , , True, False
  Application.FindFormat.Clear
  Range("E5").Value = KeepMe
End Sub

This worked

Thanks!!