Excel: Can I create a Conditional Formula based on the Color of a Cell?

Question

I'm a beginner and trying to create a formula that modifies the contents of Cell A1 based on the color of the cell in B2;

If Cell B2 = [the color red] then display FQS.

If Cell B2 = [the color yellow] then display SM.

This is conditional based on the cell fill color.

Answer 1

Unfortunately, there is not a direct way to do this with a single formula. However, there is a fairly simple workaround that exists.

On the Excel Ribbon, go to "Formulas" and click on "Name Manager". Select "New" and then enter "CellColor" as the "Name". Jump down to the "Refers to" part and enter the following:

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),1,1))

Hit OK then close the "Name Manager" window.

Now, in cell A1 enter the following:

=IF(CellColor=3,"FQS",IF(CellColor=6,"SM",""))

This will return FQS for red and SM for yellow. For any other color the cell will remain blank.

***If the value in A1 doesn't update, hit 'F9' on your keyboard to force Excel to update the calculations at any point (or if the color in B2 ever changes).

Below is a reference for a list of cell fill colors (there are 56 available) if you ever want to expand things: http://www.smixe.com/excel-color-pallette.html

Cheers.

::Edit::

The formula used in Name Manager can be further simplified if it helps your understanding of how it works (the version that I included above is a lot more flexible and is easier to use in checking multiple cell references when copied around as it uses its own cell address as a reference point instead of specifically targeting cell B2).

Either way, if you'd like to simplify things, you can use this formula in Name Manager instead:

=GET.CELL(63,Sheet1!B2)

Answer 2

You can use this function (I found it here: http://excelribbon.tips.net/T010780_Colors_in_an_IF_Function.html):

Function GetFillColor(Rng As Range) As Long
    GetFillColor = Rng.Interior.ColorIndex
End Function

Here is an explanation, how to create user-defined functions: http://www.wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel

In your worksheet, you can use the following: =GetFillColor(B5)

Answer 3

Checking a Row for a Background Color

Thank you for these great answers!

I especially liked astef's answer,

Function GetFillColor(Rng As Range) As Long
    GetFillColor = Rng.Interior.ColorIndex
End Function

which checks for a cell's background color by writing the above macro (alt+f11 to open macros), and I used that function to easily create a version of this that checks if a range of three cells in the row have a background color of yellow.

Now this might be performant or not, but it's a simple way to write the formula. This is the formula in the status column, which will check the other columns selected for any cells with yellow background, using the GetFillColor macro from astef's answer.

=IF(OR(GetFillColor([@Fees])=6,
GetFillColor([@Interest])=6,
GetFillColor([@Borrowing])=6),
 "yellow", "none")

This will return yellow to the cell if there is any yellow background (color index 6), in the cells in the GetFillColor(cell) formulas. Another advantage of writing the GetFillColor() macro is that you can use it to find what color it is you are searching for, by simply selecting an empty cell and writing =GetFillColor(your_cell) where your cell is the cell with the color of which you want the color index number.

To change this to your liking, change the GetFillColor() arguments in the =IF(OR formula above to be the cells in which might be the color, and change the 6 to any color index number you want to find, and change the two "" arguments at the end to any messages you want. The first is printed if the color is found, the second if not. Remember you can use the GetFillColor macro to return the color of any cell you like, in order to know what color index to use in the formulas.

Hope it helps. I'm open to improvement comments.