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, and 2016.
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!
When you apply conditional formatting to the cells in your worksheet, those rules can seem a bit fragile at times. For ...
Discover MoreConditional formatting is a powerful tool you can use to dynamically adjust the formatting of your worksheet. This tip ...
Discover MoreIf you want to highlight cells that contain certain characters, you can use the conditional formatting features of Excel ...
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 © 2019 Sharon Parq Associates, Inc.
Comments