by Allen Wyatt
(last updated June 19, 2021)
Gregory has a desire to specify font color in a cell based on a formula. For instance, a cell might contain a simple IF function, such as =IF(A1>A2,5,7). If the "5" is returned, he would like the font to be normal, but if the "7" is returned, he would like it red. And, it must be noted, Gregory doesn't want to use a conditional formatting rule.
Short of using a macro (as described shortly) there is no way to specify font color in a formula. The easiest way to do this—well, the easiest way that doesn't use conditional formatting—is to create a custom format for the cell containing the formula. The format itself is quite simple:
The first part of the custom format (before the semicolon) indicates how positive numbers should be formatted. The portion after the semicolon indicates how negative numbers should be formatted. (The second, negative portion is included because if it is not, then Excel applies the singular format to every value that may appear in the cell, whether it be positive or negative.)
The first portion, for positive values, indicates that if the value in the cell is "7", then the value is shown in red. If it is any other positive value (including "5"), then it is shown in a regular font. If you want additional information about how to create custom formats, you can find a great tutorial at Mynda Treacy's site:
The drawback to using custom formats in this way, of course, is that it is actually keyed to the value "7". If you want something a bit more generalized such that if any result where A1 is less than or equal to A2 results in the cell being red, then you might be interested in a macro-based approach. The following example is implemented as a user-defined function:
Function TColor(n1 As Double, n2 As Double) As Boolean If n1 <= n2 Then Application.Caller.Font.ColorIndex = 3 Else ' Set font to normal Application.Caller.Font.ColorIndex = xlAutomatic End If End Function
In order to use the UDF, just add it to your formula, in the following manner:
The UDF compares A1 to A2 and, if A1 is larger, then the text color is set to normal. If it is less than or equal to A2, then the text color is set to red.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13874) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Merging cells is a common task when creating worksheets. Merged cells can play havoc with the normal functioning of some ...Discover More
There are many ways that Excel allows you to highlight information in a cell. This tip examines a way to highlight values ...Discover More
A handy way to store latitude and longitude values in Excel is to treat them as regular time values. When it comes around ...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.