by Allen Wyatt
(last updated August 12, 2017)
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:
Figure 1. The Protection tab of the Format Cells dialog box.
Figure 2. The Protect Sheet dialog box.
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (4268) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If you want to format currency values so that Excel uses periods between groups of thousands and commas as a decimal ...Discover More
If you need to change fonts used in a lot of different workbooks, the task can be daunting, if you need to do it ...Discover More
Want to change the size of the font within a worksheet? Excel allows you to choose from a list of sizes, as well as ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.