How to Read Excel Font or Fill Color with VBA
Question details
The user wants to retrieve the exact font color or background fill color of a specific cell using a worksheet formula, which is not natively supported.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting formatting information, specifically font or fill color, from cells to utilize in worksheet functions or formulas.
- Observed behavior
- Excel worksheet formulas lack a built-in function to return color values natively, requiring a custom VBA User-Defined Function (UDF) to read and output the formatting data.
Ensure that the Developer tab is enabled in your spreadsheet application ribbon and that macros are permitted to run in your security settings.
Create a User-Defined Function (UDF) Using VBA
Use a custom VBA script to create a formula that extracts the font or fill color value from any specified cell.
Because Excel does not have a native formula to read cell colors, we can write a simple VBA macro that creates a custom function. The Application.Volatile method is included in the script so the formula updates during recalculations, though manual calculation triggers are still needed after formatting changes.
Press Alt + F11 on your keyboard to launch the VBA Editor.
In the top menu, click on 'Insert' and select 'Module' to create a blank workspace for your code.
Copy and paste the following code: Function GetColor(cell As Range, Optional ofFont As Boolean = True) As Long Application.Volatile If ofFont Then GetColor = cell.Font.Color Else GetColor = cell.Interior.Color End If End Function
Return to your worksheet. To get the font color of cell B2, type =GetColor(B2). To get the background fill color of cell B2, type =GetColor(B2, FALSE).
If you modify a cell's color later, press the F9 key to recalculate the sheet and update the formula results.
Use VBA and Macros Seamlessly in WPS Spreadsheet
WPS Office provides robust support for VBA and Macros, allowing you to use custom User-Defined Functions to read cell colors just as you would in Microsoft Excel. It offers seamless migration and a familiar UI.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsx workbook.
- 2. Enable the Developer Tab: Go to Options to ensure the Developer tab is visible on your top ribbon menu.
- 3. Access the VBA Editor: Click on 'Developer' and select 'Visual Basic' (or press Alt + F11) to write or paste your custom GetColor macro.
- 4. Apply and Save: Use your custom color formula in the worksheet and save the file as a Macro-Enabled Workbook (.xlsm).
Frequently Asked Questions
Why doesn't the formula update automatically when I change the cell color?
Changing a cell's formatting, such as its color, does not trigger Excel's calculation engine. Even with Application.Volatile in your VBA code, you must manually press F9 or calculate the sheet to display the updated color value.
What format does the VBA GetColor function return?
The provided macro returns the color as a Long integer. This number represents the numerical value of the color used internally by the application, often corresponding to an RGB code converted into a single integer.
Can I use conditional formatting based on the result of this VBA function?
Yes. Once the VBA function extracts the numerical color value into a helper column, you can reference those values in conditional formatting rules, or use them to sort and filter your data effectively.
