63

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros)

I'm using Home User Office package 2010.

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Praveen
  • 1,387
  • 1
  • 12
  • 22
  • See also http://www.excelforum.com/excel-general/744559-cell-function-detecting-color-format.html – lc. Jun 24 '14 at 09:15
  • 3
    See this [LINK](http://stackoverflow.com/questions/15887257/how-to-count-up-text-of-a-different-font-colour-in-excel) which discusses how to get information on a cell using GET.CELL function in Excel. Then navigate [THIS](http://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html) to see the syntax of GET.CELL. – L42 Jun 24 '14 at 09:15

4 Answers4

72

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:
enter image description here

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

L42
  • 19,427
  • 11
  • 44
  • 68
46

Color is not data.

The Get.cell technique has flaws.

  1. It does not update as soon as the cell color changes, but only when the cell (or the sheet) is recalculated.
  2. 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.

enter image description here

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.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • 4
    This is a great answer to show the danger of using old macros like get.cell. Very well presented! – Gaijinhunter Jul 06 '14 at 09:29
  • 10
    Good answer, as long as the user is creating original content. Not so good for those just looking for a way to get at data across thousands of rows where someone else (unfortunately) used fill color as "data". – Tim Friesen Jan 25 '16 at 20:18
  • 1
    If you started with an empty color-coded sheet, I recommend you search cells by format (based on fill color) and replace the empty cells with a different number for each color. – Felipe G. Nievinski Aug 07 '17 at 15:11
  • 5
    "Color is not data" Well.. do people record information in a spreadsheet by coloring the cells? Yes they do. Therefore color does record data, and "is data" just as much as those squiggly shapes we call "numbers". Is cell color a reliable and formula-friendly way to record data in Excel? No it is not, but that is a different question. – gwideman Mar 28 '19 at 23:04
  • 1
    color is meta-data. People need to understand that when they use color to differentiate cells. Excel has very limited capabilities of evaluating color. Even the human reader of the spreadsheet cannot tell why a cell is blue and another cell is green. It requires a legend, a system, a frame of reference. With THAT it will become data that can be analysed. – teylyn Mar 29 '19 at 09:50
11

No, you can only get to the interior color of a cell by using a Macro. I am afraid. It's really easy to do (cell.interior.color) so unless you have a requirement that restricts you from using VBA, I say go for it.

Myrddin Emrys
  • 42,126
  • 11
  • 38
  • 51
Gaijinhunter
  • 14,587
  • 4
  • 51
  • 57
2

Anticipating that I already had the answer, which is that there is no built-in worksheet function that returns the background color of a cell, I decided to review this article, in case I was wrong. I was amused to notice a citation to the very same MVP article that I used in the course of my ongoing research into colors in Microsoft Excel.

While I agree that, in the purest sense, color is not data, it is meta-data, and it has uses as such. To that end, I shall attempt to develop a function that returns the color of a cell. If I succeed, I plan to put it into an add-in, so that I can use it in any workbook, where it will join a growing legion of other functions that I think Microsoft left out of the product.

Regardless, IMO, the ColorIndex property is virtually useless, since there is essentially no connection between color indexes and the colors that can be selected in the standard foreground and background color pickers. See Color Combinations: Working with Colors in Microsoft Office and the associated binary workbook, Color_Combinations Workbook.

David A. Gray
  • 1,039
  • 12
  • 19
  • 3
    Keep in mind that the color palette in Office 2007 and later is a "themed" palette. Any work that goes into identifying color, any logic that is based on the current cell color, will need to take into account that the theme of a file can be changed to a completely different color scheme. What is blue in one theme might be red in another. Theme colors are set with a combination of accent number and shade number, not with fixed RGB values. – teylyn Sep 18 '15 at 22:48
  • Thanks for spelling that out, and reminding me if I wasn't already aware of it. The article that I cited is all about those accent numbers and themes. In any case, using the object model, it is relatively easy to determine the current color of a cell. – David A. Gray Sep 19 '15 at 03:18