Many office workers are familiar with using IF() statements to conditionally flag a row for being above a certain value, within a specific date range, etc.
However, what if your criteria for these statements were not the value of a cell, but its format—specifically, its color?
This question was posed to me by two individuals within just a few days. Therefore, in adherence to (or perhaps just shy of) Mount’s law of content marketing, I’ve decided to write a blog post on the topic.
To follow along, download the exercise file below:
Note that the file has an .xlsm extension, which means we will be using VBA to find a solution. If you’re new to VBA, I recommend starting with this introductory guide by Microsoft. Please note you must enable the Developer tab on the ribbon to access the necessary tools for building this solution.
After familiarizing yourself with VBA, navigate to the Developer tab on the ribbon and select Visual Basic. This will take you to the VBA Editor. Right-click anywhere in the menu on the left side of your screen, then select Insert > Module.
The module in VBA serves as the designated area for housing VBA code. In our case, we’ll use it to create a user-defined function (UDF), or custom function. This function will allow users to input the location of a cell and, in return, receive the color code associated with that cell. This functionality enhances the user’s ability to dynamically interact with and manipulate data in Excel based on cell color properties.
Go ahead and copy the below code into the blank script that appears in the middle of your editor.
Once you have completed your work in VBA, ensure that you save your project before exiting the application. Now it’s time to test out your new UDF!
Writing the IF() statement
A UDF created in VBA operates in a similar manner to Excel’s built-in functions. You simply enter the function, execute it, and then review the results it produces.
For demonstration purposes, I will be utilizing the GetCellColor() function within a new column of our table. This function will be used to identify the color present in the cells corresponding to each row’s Last Transaction Date. It is important to note that the GetCellColor() function is designed to determine the color of individual cells, rather than entire rows, which may not be immediately apparent given the structure of our dataset.
Each cell is assigned a specific value based on the color format detected, which is then translated into a uniform code for each color. With the numerical data now available, integrating these results into an IF() statement becomes straightforward. For instance, the orange color shade utilized here is represented by the value 22:
To manage and highlight multiple colors with corresponding messages efficiently, utilizing the IFS() and LET() functions in combination can streamline the process significantly. This approach allows for a structured and clean execution flow.
Go ahead and add these or similar color formats into your data and establish the necessary conditional logic. Note that your function may not immediately recalculate with each formatting change. This behavior stems from Excel’s recalculation mechanism, which activates primarily due to modifications in cell values or formulas rather than formatting adjustments.
To enhance the dynamic capability of this function, I integrated Application.Volatile, marking the function as volatile. As a result, Excel will recalculate it with any workbook calculation, beyond just changes to its direct inputs.
This frequent updating is advantageous for functions reliant on external data or variable conditions unrelated to the function’s parameters. However, it’s important to be cautious with this application, as overuse can degrade performance by triggering excessive recalculations.
In addition, using Application.Volatile does not prompt automatic recalculation when a cell’s fill color is modified, as Excel does not view this as a change in content or structure warranting recalculation.
Leveraging VBA event handlers might offer a solution for immediate recalculations in functions like GetCellColor(), but for now, I consider this approach sufficiently effective. I welcome any suggestions for improvement.
If you have questions about using the IF() function or any other inquiries, feel free to ask in the comments. A completed version of this workbook is available for download below:
The best approach is to teach users not to manually color cells but to write conditional formatting rules to apply colors. This may require adding helper columns to drive the rules.
This breaks the reliance on VBA, and prevents issues if changes to the workbook theme changes colors in the workbook, or even if two people have a different idea of what “color the cell green” means.
Great observation, Jon. It’s certainly true that “prevention is the best cure.”
Given that this issue arose during a couple client training sessions within just a few days, it looks like people are seeking a solution.
Nonetheless, thank you for highlighting the bigger takeaway that there are more effective methods for encoding input data for conditional formatting in Excel than using color.