Use cell's color as condition in if statement (function) Use cell's color as condition in if statement (function) vba vba

Use cell's color as condition in if statement (function)


You cannot use VBA (Interior.ColorIndex) in a formula which is why you receive the error.

It is not possible to do this without VBA.

Function YellowIt(rng As Range) As Boolean    If rng.Interior.ColorIndex = 6 Then        YellowIt = True    Else        YellowIt = False    End IfEnd Function

However, I do not recommend this: it is not how user-defined VBA functions (UDFs) are intended to be used. They should reflect the behaviour of Excel functions, which cannot read the colour-formatting of a cell. (This function may not work in a future version of Excel.)

It is far better that you base a formula on the original condition (decision) that makes the cell yellow in the first place. Or, alternatively, run a Sub procedure to fill in the True or False values (although, of course, these values will no longer be linked to the original cell's formatting).


I don't believe there's any way to get a cell's color from a formula. The closest you can get is the CELL formula, but (at least as of Excel 2003), it doesn't return the cell's color.

It would be pretty easy to implement with VBA:

Public Function myColor(r As Range) As Integer    myColor = r.Interior.ColorIndexEnd Function

Then in the worksheet:

=mycolor(A1)


Although this does not directly address your question, you can actually sort your data by cell colour in Excel (which then makes it pretty easy to label all records with a particular colour in the same way and, hence, condition upon this label).

In Excel 2010, you can do this by going to Data -> Sort -> Sort On "Cell Colour".