IF Statement in Excel Based On Cell Color - Excel Formula

In this video, I will show you how to use the IF statement in Excel based on colors to perform a logical test.

I’m using the COLORMATCH function to compare two cell colors. The function uses two arguments: the cells you want to compare and the price you want to increase. In the first example, I want to increase the price by 20% if the cell color is green. If the cell color is not green, we keep the price untouched.

The COLORMATCH returns TRUE in case of a match and gets FALSE in case of different colors. Using this method, you can handle complex logical expressions easily. In the second example, the first condition remains the same. We will increase the price if the cell color is green. But if the cell is purple, we want to apply a discount of 20%.

Chapters:

00:00 Intro
00:20 The COLORMATCH Function
00:53 How to use the IF statement based on colors
01:55 Multiple conditions - Nested IF statement based on colors

Download DataFX free function library:

If you want to use the COLORMATCH function, insert a new module, paste the code, and save a Workbook as an xlsm format.

Function COLORMATCH(cell1 As Range, cell2 As Range) As Boolean
Dim color1 As Long
Dim color2 As Long
COLORMATCH = (color1 = color2)
End Function

🔔 SUBSCRIBE if you’d like more tips and tutorials like this.

🎁 SHARE this video and spread the Excel love.

Or if you are in a hurry, please click the 👍

#excel #exceltips #exceltutorial #excelformula

[stuartlawson]

It's worth pointing out that the "colormmatch" function mentioned here is not an in-built Excel function. It has to be added via external libraries or VBA, neither of which may be possible if your Excel is locked down.

[jhanfeifoo]

How come colormatch formular is not available in my excel ?

[RSWALM]

Thank you for this detailed and very useful video. It gave me exactly what I was looking for. My question is the same as marcopio1000. when I change a cell's color I have to recopy the formula from above cell to reset the value I am calculating. I went into options and my calculations is set to Automatic. I did not see anything else to which you might have been referencing when you answered marcopio's question. Thoughts? Thanks again.

[marcopio]

Thank you!! i change a color afterwards, the formula do not update automatically, i have to click in the cell... is there a way to let it update like all the other excel formulas?

[AML]

Is it possible to use the colormatch function to count how many cells within a range are a certain color?

[Spaatz]

Hi. Your tool set looks great. I am attempting to install the ExcelDna.IntelliSense64.xll fileand DFX.xlam extension to Excel 2010 on a Windows 7 system. I am getting file extension mismatch and a security certificate warning. The ExceDna.IntelliSense64.dll file opens at a corrupted text Excel file. Any thoughts how to fix?

[kevinmontanez]

COLORMATCH function is not working. Idk, it always returns #NAME? error. I exactly copied your formula.