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

Question

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

Answer 1

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.

Answer 2

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