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 May 6, 2015)
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Conditional formatting can be used to draw your attention to certain cells based on what is within those cells. This tip ...Discover More
Conditional formatting can be used to draw attention to all sorts of data based upon the criteria you specify. Here's how you ...Discover More
If an error exists in a formula tucked inside a conditional format, you may never know it is there. There are ways to find ...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.