Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Highlighting Values in a Cell.
by Allen Wyatt
(last updated October 18, 2014)
Figure 1. The New Name dialog box.
=CellHasNoFormulaThe formula returns True or False, depending on whether there is a formula in the cell or nor. If there is no formula, then True is returned and whatever format you specify is applied to the cell. Another approach is to use a user-defined function to return True or False, and then set up the conditional format. You could use a very simple macro, such as the following:
Function IsFormula(Check_Cell As Range) As Boolean Application.Volatile IsFormula = Check_Cell.HasFormula End FunctionYou can then specify the conditional formatting rule type as Use a Formula to Determine which Cells to Format and enter the following formula in the condition if, for instance, you are conditionally formatting cell C1:
=NOT(IsFormula(C1))The formula returns True if there is no formula in the cell, so the conditional format is applied. The only downside of using any of these formulas to determine if a formula is in the cell is that it cannot determine if the formula in the cell has been replaced with a different formula. This applies to both the macro approach and the defined formula approach. A totally different approach is to rethink your worksheet a bit. You can separate cells for user input from those that use the formulas. The formula could use an IF function to see if the user entered something in the user input cell. If not, your formula would be used to determine a value; if so, then the user's input is used in preference to your formula. This approach allows you to keep the formulas you need, without them being overwritten by the user. This results in great integrity of the formulas and the worksheet results.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9270) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Highlighting Values in a Cell.
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!
Keyboard shortcuts can save time and make developing a workbook much easier. Here's how to apply the most common of ...Discover More
Excel allows you to perform math using times as operands. If you subtract a later time from an earlier time, you should ...Discover More
Need to merge a bunch of cells together on a regular basis? You'll love the two macros in this tip which can make short ...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.