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

I am trying to get a cell to perform a function based on the hilight color of a cell.

Here is the function I currently have:

=IF(A6.Interior.ColorIndex=6,IF(ROUNDDOWN(IF(M6<3,0,IF(M6<5,1,IF(M6<10,3,(M6/5)+2))),0)=0,0,ROUNDDOWN(IF(M6<3,0,IF(M6<5,1,IF(M6<10,2,(M6/5)+2))),0)),IF(ROUNDDOWN(IF(M6<7,0,IF(M6<10,1,M6/5)),0)=0,0,ROUNDDOWN(IF(M6<7,0,IF(M6<10,1,M6/5)),0)))

Just so you don't have to read through all of that, here's a more simple example

=IF(A6.Interior.ColorIndex=6,"True","False")

All that his is returning is #NAME? . Is there any way that I can do this as a function in a cell or is VBA absolutely required?

Thanks,

Jordan

I had a similar problem where I needed to only show a value from another Excel cell if the font was black. I created this function: `Option Explicit

Function blackFont(r As Range) As Boolean If r.Font.Color = 0 Then blackFont = True Else blackFont = False End If

End Function `

In my cell I have this formula: =IF(blackFont(Y51),Y51," ")

This worked well for me to test for a black font and only show the value in the Y51 cell if it had a black font.

The only easy solution that I have applied is to recreate the primary condition that do the highlights as an IF condition and use it on the IF formula. Something like this. Depending on the highlight condition the formula will change but I think that should be recreated (es. highlight greater than 20).

=IF(B3>20,(B3)," ")

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