Prevent Excel VBA Formatting from Locking a Cell
Question details
Keep cell C3 unlocked when a VBA macro changes its formatting.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro formats cell C3, which needs to remain editable when the worksheet is protected.
- Observed behavior
- ClearFormats resets the cell's Locked property, so a later macro run fails after the worksheet is protected.
Open the workbook in Excel and identify the worksheet containing C3. If the sheet is protected, use Review > Unprotect Sheet and enter its password if prompted before changing the cell's protection or formatting.
Format C3 directly without using ClearFormats
Use this approach when you know which formatting properties the macro needs to change and want C3 to remain unlocked.
In Excel, press Alt+F11, open the relevant module, and locate the macro that formats C3.
Replace the Select, Selection, and ClearFormats statements with direct references. For example, adjust these properties to suit your worksheet: With ActiveSheet.Range("C3") .NumberFormat = "General" .HorizontalAlignment = xlGeneral .Font.Bold = False .Borders.LineStyle = xlNone .Locked = False End With
With the target worksheet active and unprotected, run the macro from Developer > Macros. Then select Review > Protect Sheet and test whether C3 remains editable.

Clear C3 formatting and restore its unlocked setting
Use this option when you want to reset formatting rather than specify each property individually.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easier to move everyday spreadsheet work between applications. It does not fix Excel's VBA behavior; check macro support in your WPS version before migrating VBA-dependent workbooks.

Frequently Asked Questions
Why does ClearFormats lock C3?
ClearFormats restores default formatting, including the cell's Locked property. The property takes effect when the worksheet is protected.
How can I check whether C3 is unlocked?
Select C3, open Format Cells, and check the Protection tab. The Locked checkbox must be cleared for the cell to remain editable under worksheet protection.
Can I run the example macro on a protected worksheet?
The example sets the Locked property, so unprotect the worksheet through Review > Unprotect Sheet before running it. Protect the sheet again afterward.
Do I need to select C3 before formatting it with VBA?
No. Use a direct reference such as ActiveSheet.Range("C3") in a With block. Make sure the intended worksheet is active, or replace ActiveSheet with an explicit worksheet reference.




