Written by Allen Wyatt (last updated November 26, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Jose knows he can sort by color in Excel, but wonders if he can use the color of the text within a cell in a formula. For instance, the color of the contents of cell A1 is determined by conditional formatting. In another cell, he'd like to return -1 if A1 is shown in white, 2 if it is black, and 3 if it is blue.
When dealing with conditional formatting, detecting colors applied by the formatting rules can be troublesome. (In other words, it cannot be done reliably across differing versions of Excel.) In this case, it would be much easier to simply recreate, in the formula, the conditional formatting rule and return the values desired. In other words, use the same formulas you used in the conditional formatting rule to determine the value to be returned. You can, if necessary, combine multiple rules into a single formula by wrapping them within an IF statement.
You could even go a step further, if desired. For instance, let's say that you develop a formula that you use in the cells of column B. The formula returns -1, 2, or 3, based on whatever criteria you want to use. Once that formula is in place, you could modify the conditional formatting rules used in column A so that it adjusts text color based upon the values in column B. That way, you aren't dealing with having two sets of formulas for the two columns.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (43) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
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!
Conditional formatting is a great tool for changing the format of cells based on whether certain conditions (rules) are ...
Discover MoreConditional formatting is a powerful tool you can use to dynamically adjust the formatting of your worksheet. This tip ...
Discover MoreIf you just updated your copy of Excel, you may have noticed some differences in how the program handles applying ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments