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!
Want to know where duplicates are in a list of names? There are a couple of ways you can go about identifying the ...
Discover MoreConditional formatting is a great feature in Excel. Here's how you can copy conditional formats from one cell to another ...
Discover MoreNeed to know if a particular cell contains a date value? Excel doesn't have a worksheet function to determine this ...
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 © 2018 Sharon Parq Associates, Inc.
Comments