Excel formula to get cell color
As commented, just in case the link I posted there broke, try this:
Add a Name(any valid name) in Excel's Name Manager under Formula tab in the Ribbon.
Then assign a formula using GET.CELL function.
=GET.CELL(63,INDIRECT("rc",FALSE))
63 stands for backcolor.
Let's say we name it Background so in any cell with color type:
=Background
Result:
Notice that Cells A2, A3 and A4 returns 3, 4, and 5 respectively which equates to the cells background color index. HTH.
BTW, here's a link on Excel's Color Index
Color is not data.
The Get.cell technique has flaws.
- It does not update as soon as the cell color changes, but only when the cell (or the sheet) is recalculated.
- It does not have sufficient numbers for the millions of colors that are available in modern Excel. See the screenshot and notice how the different intensities of yellow or purple all have the same number.
That does not surprise, since the Get.cell uses an old XML command, i.e. a command from the macro language Excel used before VBA was introduced. At that time, Excel colors were limited to less than 60.
Again: Color is not data.
If you want to color-code your cells, use conditional formatting based on the cell values or based on rules that can be expressed with logical formulas. The logic that leads to conditional formatting can also be used in other places to report on the data, regardless of the color value of the cell.