Excel Formula Based on Cell Background Colour

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

Step 1
formula-cell-colour-step1First 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
End Function

Step 2
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.

=CellColour(4,4)

The result of this formula will give the numerical value for the background colour yellow. The value should be 6.

Step 3
formula-cell-colour-step2Ok, 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”

=If(CellColour(4,4)=6,"Yellow","Not 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.

You can also count the number of cells that contain black font, or any other colour.

Please do provide feedback on my articles, or whether you know of a easier way to do this.


18 thoughts on “Excel Formula Based on Cell Background Colour

  1. Pingback: Tweets that mention Excel Formula Based on Cell Background Colour | Excel Geek -- Topsy.com

  2. G

    This code is really useful, but I find in Excel 2007 that if I update the colour of the cell, the code does not update automatically (calculations are on and I hit F9). I need to hit F2 and enter to update the code when I change the colour of the cell. Am I missing a step?

    Thanks

  3. Jon

    This works for colored rows.

    Function CurrentCellColor() As Long
    Select Case TypeName(Application.Caller)
    Case “Range”
    Dim r As Range
    Set r = Application.Caller
    v = r.Cells(1, 1).Interior.ColorIndex
    Case “String”
    v = “-1”
    Case “Error”
    v = “-1”
    Case Else
    v = “-1”
    End Select
    CurrentCellColor = v
    End Function

  4. RolandC

    The function CellColour works fine but I experience exactly the same problem as G (no automatic update of the colour code when I change the background colour). Am I also missing a step ? Thanks.

  5. Audrey

    Is there a way to “copy” this equation and update to the next cell automatically, similiar to drag & copy functionality? I need to return text based on color for over 50 columns and 200 rows. There must be an easier way then updating this equation for each cell.

    Thanks!

  6. BernieMac

    try =CellColour(ROW(C101),COLUMN(C101))
    the row and column functions return the row number and column number of the cells and along dragging for relative cell addresses

    Regards

    BernieMac

  7. GarethB

    Hi – thanks for this useful info – I’ve found that this doesn’t work when the cells I am looking at are a certain colour because of conditional formatting – do you know if it’s possible to do this? Thanks.

  8. Barry Bowden

    I keep getting a -4142 number instead of the background color of the cell I am checking.

    What have I done wrong?

    Barry

  9. Tony

    To use an ‘ordinary’ address try
    Function Color(Target As Range) As Long
    Color = Cells(Target.Row, Target.Column).Interior.ColorIndex
    End Function

  10. Lizelle

    I used conditional formatting to highlight certain cells, now I need to use a vlookup based on colour. Can it be done?

  11. Craig Chamberlain

    This DOES NOT work for me.
    Sure, I entered the Module, entered the CellColour function with the BACKWARDS (row, column) and get back a number, BUT, if I use this inside another function, all I get is zero! I need to count the number of cells down a list of names that is colour coded that shows their assignments by colour, and enter =COUNTIF(my range,CellColour=38) which should count the number of pink coloured cells, and I count 9 manually, but this formula will only return 0.
    Completetly useless.
    I did find out that a cell that has no fill colour, CellColour returns “-4142”, but sometimes when in a differnt column it would return “–4142”! One time it comes back with 1 negative sign, the next time it’s with two negative signs!! What gives?? This function is not consistent. Something is going on inside of Excel that can’t be figured out, and comes back with wierd results.
    If a custom made function can’t be called inside of a common function, then we need something better.
    Craig

  12. Pepe Agius

    only problem with this is that when the color changes it does not upodate value, how can we change the code to update atomatically?

  13. Benjy

    To refresh the cells try:

    Sub RefreshCells()

    ‘ RefreshCells Macro

    ‘ Select the cells you want to refresh

    Range(“Enteryourrangehere”).Select

    ‘ Update selected area to chosen format or value etc

    Dim mycell As Range

    For Each mycell In Selection
    mycell.Formula = mycell.Formula
    Next mycell

    End Sub

  14. Daniel

    I followed the instruction but I do not receive the value 6 after =CellColour(4,4) I just get #VALUE! why is that?

  15. Pingback: R,C address in another worksheet

  16. Abdullah Sarwari

    is there any way to give specific color to a cell when there is number in the cell. if function

Leave a Reply

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