Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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.

like image 332
Jay 0 Avatar asked Nov 15 '13 19:11

Jay 0


People also ask

Can you conditional format a cell based on the color of another cell?

Highlight Cells Based on Another Cell To highlight cells based on another cell's value, you can create a custom formula within a conditional formatting rule. Select the range you want to apply formatting to. In the Ribbon, select Home > Conditional Formatting > New Rule.


Video Answer


2 Answers

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) 
like image 120
Derrik Avatar answered Oct 13 '22 08:10

Derrik


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)

like image 22
astef Avatar answered Oct 13 '22 06:10

astef