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.


1,494 thoughts on “Excel Formula Based on Cell Background Colour

  1. 高雄出發

    Today, while I was at work, my sister stole my
    iPad and tested to see if it can survive a 30 foot drop, just so she can be a youtube sensation.
    My iPad is now broken and she has 83 views. I know this
    is totally off topic but I had to share it with someone!

  2. website

    Howdy! This post could not be written much better!

    Looking at this article reminds me of my previous roommate!
    He continually kept preaching about this. I will forward this information to
    him. Pretty sure he will have a good read. I appreciate
    you for sharing!

  3. adult frind finder

    Hey, I think your website might be having browser
    compatibility issues. When I look at your blog
    in Ie, it looks fine but when opening in Internet Explorer, it
    has some overlapping. I just wanted to give you a quick heads up!
    Other then that, amazing blog!

  4. remote access

    Hello, i feel that i saw you visited my website so i came to go back the desire?.I’m trying to in finding
    things to improve my website!I assume its adequate to use a few
    of your ideas!!

  5. Best CBD Oil

    We stumbled over here different web address and thought I might as well check things out. I like what I see so i am just following you. Look forward to looking over your web page repeatedly.|

  6. smoothie maker

    I was wondering if you ever considered changing the structure of your
    website? Its very well written; I love what youve got to say.

    But maybe you could a little more in the way of content so
    people could connect with it better. Youve got an awful lot of text for
    only having one or 2 images. Maybe you could space it out better?

  7. Alex

    I am really glad to glance at this webpage posts which carries lots of
    useful information, thanks for providing these data.

  8. hikvision cctv installers

    Today, I went to the beach front with my kids.
    I found a sea shell and gave it to my 4 year old daughter
    and said “You can hear the ocean if you put this to your ear.” She
    placed the shell to her ear and screamed. There was a hermit
    crab inside and it pinched her ear. She never wants to go back!
    LoL I know this is completely off topic but I had to tell someone!

  9. bianca ingrosso porr

    nicole berg porr bianca ingrosso porr dejta singlar porr leijon porr mammor singlar 50+ dvarg porr gratis porr bror valdtar
    syster dejting bonder fillipinsk porr singlar i
    narheten fragor att stalla pa dejt porr i danmark akademiska singlar
    hitta singlar nara dig taj porr dejting bollnas singlar boras porr norsk
    porr pa viasat andra dejt dejting happy pancake se porr gratis samson porr tecknad porr het
    porr porr youtube skansk porr porr gotland ridskolan 1
    porr etisk porr forsta kyssen dejt jockeboi porr bra porr sidor dejting malmo jocke jonna porr film ny svensk porr mom son porr animal gay porr porr
    stora kukar mia khalifa porr pixee fox porr hund porr dejt spel arab porr fragor att stalla pa dejt
    mogen dejting feministiskt porr romantisk porr far och dotter
    porr zoo porr

  10. 청주출장안마

    Does your website have a contact page? I’m having trouble locating it but, I’d like to send you an email.
    I’ve got some ideas for your blog you might be interested in hearing.
    Either way, great blog and I look forward to seeing it grow over time.

  11. Lee Kok Weng

    Undeniably believe that which you said. Your favourite reason seemed to be on the internet the easiest thing to remember of.
    I say to you, I certainly get annoyed while folks consider concerns that they plainly do not understand
    about. You managed to hit the nail upon the highest and outlined out the entire thing
    without having side-effects , other people could take
    a signal. Will probably be again to get more. Thank you

  12. Cham la so tu vi tron doi

    I was suggested this website by means of
    my cousin. I’m now not positive whether or not this submit is
    written by way of him as nobody else recognize such special about
    my problem. You’re wonderful! Thank you!

  13. ask

    either ask man city fast week threat magazine recently mention manager teacher those exactly resource beyond everyone production young class former especially week majority
    soon daughter test exactly method sound thousand store money
    hit list customer after thus visit relationship disease production course claim determine physical up kind
    her never wife

  14. sling tv

    Hello my family member! I wish to say that this article is amazing, nice written and come with approximately all vital
    infos. I would like to look more posts like this .

  15. sling tv

    My brother recommended I might like this web site.
    He was entirely right. This post actually made my day.

    You can not imagine just how much time I had spent for this information!
    Thanks!

  16. term

    agreement term develop explain image police market especially plant international skin activity Republican admit strong employee their debate set begin sense development plant already type I provide population him
    understand today option owner former great finish real material culture during rule important discussion owner its answer sense performance
    off address rise

  17. modabet

    Hello, i think that i saw you visited my wweb site
    so i came to “return the favor”.I amm attempting to find things to enhance my site!I
    suppose its ok to use some off your ideas!!

Leave a Reply

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