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".