Use Excel Cell Fill Color in an IF Formula with GET.CELL or VBA
Question details
The user needs an Excel IF formula to return a result based on whether another cell has a specific fill color, such as yellow.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses cell background colors as visual indicators, and the user wants formulas to react to those indicators.
- Observed behavior
- Standard Excel worksheet formulas cannot directly read a cell's fill color.
Identify the exact target color before creating the formula or macro. If the fill is applied by conditional formatting, check the conditional-formatting rule because the cell's stored Interior.Color may differ from its displayed color.
Use a named GET.CELL formula to return the fill color index
Use this legacy macro-function approach when you need a formula-based result and can work with a color index instead of an RGB value.
GET.CELL(63,reference) returns the cell's fill color index. Because GET.CELL is a legacy Excel macro function, create it through Name Manager instead of entering it directly in a worksheet cell.
Formatting changes may not trigger recalculation automatically. Press F9 after changing a fill color, and save the workbook as a macro-enabled file if your Excel version requires it.
In Excel, select Formulas > Name Manager > New to create a workbook-level named formula.
Enter FILLCOLOR as the name. In Refers to, enter =LAMBDA(reference,GET.CELL(63,reference)), then select OK.
In a worksheet cell, enter a formula such as =IF(FILLCOLOR(A1)=6,B1,""). Replace 6 with the color index used by the fill in A1 and adjust B1 for the value you want to return.
After changing the fill color in the referenced cell, press F9 to refresh the formula result if it does not update immediately.
Use a VBA function to compare the cell's RGB fill color
Use VBA when you need more reliable detection of a specific fill color, especially for an exact yellow RGB value.
Detect Fill Colors with VBA in WPS Spreadsheets
WPS Spreadsheets can use VBA-compatible macros to read a cell's fill color and return a value for use in an IF formula. Create a small color function in the Visual Basic Editor, save the workbook as a macro-enabled file, and use the function in your worksheet logic.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook, and select the spreadsheet worksheet that contains the colored cells.
- 2. Open the VBA editor: Go to the Developer tab and select Visual Basic, then choose Insert > Module.
- 3. Create the color function: Enter a function such as Function FillColor(cell As Range) As Long: FillColor = cell.Interior.Color: End Function in the new module.
- 4. Save as a macro-enabled workbook: Select Menu > Save As and choose the XLSM macro-enabled workbook format so the VBA code is retained.
- 5. Add the IF formula: Return to the sheet and use a formula such as =IF(FillColor(A1)=65535,B1,"") to return the value in B1 when A1 is yellow.
Frequently Asked Questions
Can a normal Excel IF formula detect a yellow cell background?
No. Standard worksheet functions cannot directly read cell formatting. Use a named GET.CELL formula or a VBA function that reads the cell's Interior.Color value.
Why does my GET.CELL color formula not update after I change the fill color?
Formatting changes do not always trigger recalculation for GET.CELL. Press F9 to recalculate the workbook after changing the fill color.
What number represents yellow in an Excel VBA fill-color test?
Standard yellow is commonly returned as 65535 by Interior.Color, so you can use =IF(FillColor(A1)=65535,B1,""). Verify the value if the workbook uses a custom theme color.
Does GET.CELL work when the color comes from conditional formatting?
Not reliably for the displayed conditional-formatting result. GET.CELL and Interior.Color generally read the direct cell fill, so it is usually better to test the condition that triggers the formatting.
Do I need to save the file as XLSM for a VBA color function?
Yes. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) to preserve the VBA code, and enable macros when you open the file.
