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**

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`

`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**

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”

`=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.

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

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

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

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.

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!

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

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.

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

What have I done wrong?

Barry

To use an ‘ordinary’ address try

Function Color(Target As Range) As Long

Color = Cells(Target.Row, Target.Column).Interior.ColorIndex

End Function

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

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

Great change to the macro, Tony. It’s much nicer now that it’s been generalized.

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

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

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

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

Pingback: R,C address in another worksheet

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