Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Protecting Your Conditional Formatting Rules.
by Allen Wyatt
(last updated June 21, 2018)
Larry wrote about a problem he was encountering with protecting a worksheet he developed. He has cells that contain both formulas and conditional formatting. He can protect both of them in a worksheet, but if someone selects a cell and copies it to another worksheet, the conditional formatting is visible.
When you copy a protected cell from one sheet to another, if the formulas in the source cell were hidden in the protection process, then the results of the formulas are pasted, unprotected, into the target cells. This is probably no big deal, as you wanted the formulas—not the results—protected.
Excel is not as protective about conditional formats, however. The conditional formats of the cells that you paste, since they are in an unprotected worksheet, can be viewed and modified, as desired. This can be a problem if the conditional formats contain formulas that you want to also keep private.
The only way around this problem is to disable the ability to copy anything from your protected worksheet. You do this through the use of a macro, added to the worksheet object, that would disable copying.
Private Sub Worksheet_Deactivate() Application.CutCopyMode = False End Sub
This macro works because anytime the worksheet is deactivated (meaning, the target worksheet is selected), then CutCopyMode is set to False. This results in the "marching ants" that appeared around the source cells when the user pressed Ctrl+C being removed, and pasting therefore no longer possible. Copying and pasting on the same worksheet is still fine; just not to a different (unprotected) worksheet.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3301) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Protecting Your Conditional Formatting Rules.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
You can use conditional formatting to add shading to various cells in your worksheet. This tip shows how you can shade ...Discover More
If you have a data table in a worksheet, and you want to shade various rows based on whatever is in the first column, ...Discover More
Conditional formatting is a great tool. You may need to use this tool to tell the difference between cells that are empty ...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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.