Locking the Background Color

Mander has a worksheet for which he wants to change the background color. Once changed, he wants the color to be "unchangeable" by anyone else. Mander knows he can use worksheet protection, but that stops people from actually using the worksheet. He doesn't want to stop use of the worksheet; he just doesn't want the background changed once he sets it.

There are a couple of ways you can go about achieving what you want. The first is to examine how you are applying your worksheet protection. The background color is considered a format of a cell, so all you need to do is to make sure you protect the worksheet and allow all changes except to the formatting. Follow these steps:

1. Select the cells whose contents you want the user to be able to change. (You can select all the cells in the worksheet, if desired.)
2. Display the Home tab of the ribbon.
3. Click Format in the Cells group, and then choose Format Cells. Excel displays the Format Cells dialog box.
4. Make sure the Protection tab is displayed. (See Figure 1.)



Figure 1. The Protection tab of the Format Cells dialog box.

5. Clear the Locked checkbox.
6. Click on OK to close the dialog box.
7. With the Home tab of the ribbon still displayed, click Format in the Cells group, and then choose Protect Sheet. Excel displays the Protect Sheet dialog box. (See Figure 2.)



Figure 2. The Protect Sheet dialog box.

8. Select all the check boxes except those related to formatting (Format Cells, Format Columns, and Format Rows).
9. You do not need to enter a password unless you want to; just click on OK.

Now people can make any changes to whatever cells were selected in step 1, with the exception of changing any formatting. This means that your background color remains protected.

There is a problem with this approach—you may want to allow the user to change other cell formatting, such as how numbers, dates, or times are displayed. Since you've protected the formatting of the sheet, however, these things cannot be changed.

To get around this, the only thing you can do is to use a macro-based approach. A simple way is to create an event handler that modifies the background color of the worksheet whenever it is activated. This way, if someone modifies the background color, the next time the worksheet is activated those changes will be effectively done away with. Here's an example that sets the background color to a turquoise color:

Private Sub Worksheet_Activate()
    With Cells.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 192, 192)
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

This macro needs to be added to the code sheet for whatever worksheet you want it used with. This particular event handler changes the background of all cells. If you want to affect only a certain number of cells, then you can change the structure (the With structure) so that only specific cells are modified.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (4268) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.