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.
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 reference cell colors external to your current workbook, there is no way to do it using Excel functions. ...Discover More
Formatting a cell could, if you desire, also include the indentation of information within the cell. This tip examines ...Discover More
Enter a fraction into Excel, and you may be surprised that the program reduces the faction to its simplest form. If you ...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.