This article will explain how to have a formula run based entirely upon whether an excel cell has a particular background colour.
To do this will require the use of the formula, as well as a short custom function that needs to be written. For the purposes of this example the formula will show Yes or No depending on the cell colour. But you could simply replace this part with whatever calculation you want to run depending on whether it is true or false.
Guide to Creating an Excel Formula Based on Cell Background Colour
First of all we need to create a custom Excel function. Creating a custom excel function will then return a numeric value based on the cell’s background colour. In Excel each background colour has it’s own unique numeric value that corresponds to that colour. We need this value for the formula to work. To create a function, you need to insert a module within the visual basic environment. Once you have done this, type the following text to create the custom function.
Function CellColour(Irow As Integer, Icol As Integer) As Long
CellColour = Cells(Irow, Icol).Interior.ColorIndex
Now the function is written we can switch back to the worksheet. Next step is to get the numerical value of a background colour. It is important to note here that when typing the formula we will be referencing a cell by its numerical value, for example, 4,4 instead of D4. It has to be done this way because of the function. So, click in cell D4 and change its background colour to yellow. Once you have done this type the following formula into cell E4, then push enter. Note the 4,4 instead of D4.
The result of this formula will give the numerical value for the background colour yellow. The value should be 6.
Ok, great stuff. We now have the numerical value for the background colour. Let’s assume you wish to run a formula in cell E4, and the formula is based upon the background colour in cell D4 (or 4,4) being either yellow or not yellow. You can overwrite the formula written earlier in cell E4 and replace it with the following code and then push return. Note the “If(CellColour(4,4)=6”. This is essentially saying “if the background colour in cell D4 is yellow”
You should get the following text displayed, “Yellow”. You get this because the background is yellow, so the answer is true. If you were to change the background colour then your answer would be “Not Yellow”.
So we now have a formula based upon whether a background colour is yellow. Great. But you could also perform a calculation instead of simply displaying “Yellow”. Whatever calculation you want to perform needs to replace the “Yellow” section in the formula. This is because this the argument if value is true.
Please do provide feedback on my articles, or whether you know of a easier way to do this.