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 lt 3 0 IF M6 lt 5 1 IF M6 lt 10 3  M6 5  2    0  0 0 ROUNDDOWN IF M6 lt 3 0 IF M6 lt 5 1 IF M6 lt 10 2  M6 5  2    0   IF ROUNDDOWN IF M6 lt 7 0 IF M6 lt 10 1 M6 5   0  0 0 ROUNDDOWN IF M6 lt 7 0 IF M6 lt 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

User · Answer

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

User · Answer

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

User · Answer

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 If End 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

User · Answer

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

User · Answer

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 gt 20  B3

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

Examples related to excel

Examples related to function

Examples related to vba