Change a Cell Color with VBA on a Protected Excel Worksheet
Question details
The user needs a VBA macro to change a cell's fill color while an Excel worksheet is protected.

- 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.
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.
Unprotect the worksheet before changing the cell color
Use this approach to temporarily remove protection for the color change and restore it afterward.
In Excel, open Developer > Visual Basic and locate the macro that changes the cell color.
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
Run the macro from Developer > Macros, then confirm that the cell color changed and the worksheet is protected again.

Protect the worksheet with UserInterfaceOnly enabled
Use this option when macros need to modify protected cells repeatedly while the user interface remains protected.
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.

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.




