Equations Based on Cell Colour

I was wondering if there is any way of entering code in VBA or within a document to specify that if a certain cell is coloured yellow, then perform a certain equation on that cell or one adjacent to it.

In short, I wish to multiply the earnings of a person if they are in the top 5 sellers. Cells related to people in the top 5 are highlighted yellow.

I therefore wish to be able to colour these 5 cells yellow, automatically multiplying the values by 25%.

Is this possible.

Many thanks

3 thoughts on “Equations Based on Cell Colour

  1. Excel Geek


    Thanks for your question.

    You will need to add the following piece of VBA code to a module within Excel:

    Function CellColour(Irow As Integer, Icol As Integer) As Long
    CellColour = Cells(Irow, Icol).Interior.ColorIndex
    End Function

    This creates a custom function based upon cell colours which we will use later to work out the equation.

    In the worksheet that contains your data type the following function into the cell that you wish to display results in for the first row. I have assumed that the earnings figures are in column A starting at row 2. So my function is written as so:


    In the brackets immediately after the cellcolour reference is 2,1. This refers to cell A2. For cell A3 it would be 3,1 and for cell A4 it would be 4,1 and so on.

    What this function does is if the cell colour is yellow (represented by the number six) then perform the calculation to add 25% to the earnings figure. Simply copy this formula down your range of cells, and then whenever the cell background is yellow it will calculate the earnings. If the background is not yellow, then the cell will be left blank.

    Remember when copying function down across your range that you will need to change each row cell reference in the first brackets (2,1) and so on.

    Hope this helps!

  2. Steven Condotta

    When you copy the formula down your range of cells, is there any way to have the formula reference the proper cell without having to manually change the reference cell?
    Ex. (2,1)…(3,1), (4,1) can this be done automatically?

Leave a Reply

Your email address will not be published. Required fields are marked *