Use Excel GET.CELL to Read a Cell Background Color
Question details
The user wants to return an Excel cell background color code and use it in an IF calculation without receiving a #VALUE! error.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to test a cell’s fill color as part of a worksheet calculation.
- Observed behavior
- Entering GET.CELL directly in a worksheet formula returns a #VALUE! error.
GET.CELL is a legacy Excel 4.0 macro function, so it must be stored in a defined name rather than entered directly in a worksheet cell. Identify the cell whose fill color you want to evaluate before creating the name.
Create a defined name that returns the Excel cell fill color
Use this method when GET.CELL returns #VALUE! because it was entered as a standard worksheet formula.
The GET.CELL function with argument 63 returns the background fill color index of a referenced cell. Excel allows this legacy macro function through Name Manager, where the defined name can then be used in worksheet formulas.
In Excel, select a result cell, then go to Formulas > Name Manager > New.
Enter GetColor in the Name box. In the Refers to box, enter =GET.CELL(63,Sheet1!A1), replacing Sheet1!A1 with the cell whose background color you want to read.
Select the cell next to the colored cell and enter =GetColor. Excel returns the fill color index for the referenced cell.
Enter a formula such as =IF(GetColor=6,"Yellow","Other") and replace 6 with the color index returned for the fill color you want to test.
Update the defined name when the target cell changes
Use this option when the color code is always returned for the same cell after you copy the formula to another row or column.
Work with Excel-compatible spreadsheets in WPS Office
GET.CELL is a legacy Microsoft Excel macro function, so its behavior depends on Excel’s support for Excel 4.0 macro functions. WPS Office is a free, lightweight alternative for creating, editing, and sharing everyday spreadsheets with a familiar interface and seamless migration from Microsoft Office files.
- 1. Download WPS Office: Select Download WPS Office Free and install WPS Office on your device.
- 2. Open your spreadsheet: Launch WPS Spreadsheets and choose Menu > Open to select your Excel .xlsx or .xls file.
- 3. Review formulas and formatting: Select the relevant worksheet and check formulas, cell fills, and conditional formatting from the Home tab.
Frequently Asked Questions
Why does GET.CELL return #VALUE! in an Excel worksheet?
GET.CELL is an Excel 4.0 macro function, not a normal worksheet function. Add it through Formulas > Name Manager > New, then use the defined name in a cell.
What does GET.CELL(63, reference) return?
GET.CELL with type number 63 returns the background fill color index for the referenced cell. The number can be compared in an IF formula.
How can I use a cell color in an IF formula in Excel?
Create a defined name such as GetColor with =GET.CELL(63,Sheet1!A1), then use a formula such as =IF(GetColor=6,"Yellow","Other"). Replace 6 with the returned color index you need.
Why does the GET.CELL result not change after I apply a new fill color?
Excel may not recalculate a GET.CELL defined name after formatting-only changes. Press F9 to recalculate, or edit a cell and press Enter.
Can GET.CELL identify conditional formatting colors?
GET.CELL may not reliably return the displayed color produced by conditional formatting. It is most suitable for checking a cell’s directly applied fill color.
