VBA & Macro Problems

Change a Cell Color with VBA on a Protected Excel Worksheet

Steven ParkerSteven Parker Nov 20, 2026 868 views

Question details

The user needs a VBA macro to change a cell's fill color while an Excel worksheet is protected.

Change a Cell Color with VBA on a Protected Excel Worksheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro changes a shape and displays a list box before updating a cell's color on a protected worksheet.
Observed behavior
The macro fails when it attempts to change the cell color while the worksheet is protected.
Before you start

Identify the worksheet, target cell, and worksheet protection password, if one is set. Note any protection permissions you need to preserve when the sheet is protected again.

Solution 1Recommended

Unprotect the worksheet before changing the cell color

Use this approach to temporarily remove protection for the color change and restore it afterward.

1
Open the VBA editor

In Excel, open Developer > Visual Basic and locate the macro that changes the cell color.

2
Wrap the color change

Replace the failing color-change section with the following pattern, using your sheet name, cell, and password: Sub ChangeProtectedCellColor() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") On Error GoTo RestoreProtection ws.Unprotect Password:="your_password" ws.Range("A1").Interior.Color = RGB(255, 255, 0) RestoreProtection: If Err.Number <> 0 Then MsgBox "Macro error: " & Err.Description Err.Clear On Error Resume Next ws.Protect Password:="your_password" If Err.Number <> 0 Then MsgBox "Could not protect the sheet: " & Err.Description On Error GoTo 0 End Sub

3
Test the macro

Run the macro from Developer > Macros, then confirm that the cell color changed and the worksheet is protected again.

Unprotect the worksheet before changing the cell color
Protection settings: The example assumes the sheet should be protected afterward. Add the appropriate Protect arguments to restore any custom permissions. A password stored in VBA code may be visible to someone who can inspect the project.
Free Microsoft Office alternative

Work with spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets. Its Microsoft Office format support helps you move everyday files between suites.

Edit and format spreadsheet cells in WPS SpreadsheetsOpen and save Microsoft Excel formats, including XLSXUse a familiar interface for everyday spreadsheet workMove compatible Office files between applications
microsoft office alternative - wps office

Frequently Asked Questions

Why does the color change fail on a protected sheet?

Worksheet protection can prevent VBA from changing a cell's formatting. Unprotect the sheet before the change or protect it with UserInterfaceOnly:=True so macros can make changes.

Where do I put the UserInterfaceOnly code?

In Developer > Visual Basic, open ThisWorkbook and add it to a Workbook_Open event. Save the file as .xlsm and enable macros when reopening it.

Will UserInterfaceOnly remain enabled after I reopen the workbook?

No. Reapply it in Workbook_Open each time the workbook opens, and include any other protection settings your sheet requires.

How can I find the Protect and Unprotect syntax for my sheet?

Use Developer > Record Macro, unprotect the worksheet, change the cell color, and protect it again. Stop recording and inspect the generated code in Developer > Visual Basic.