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

Not to my knowledge, I believe VBA will be necessary. – Daniel Sep 13 '13 at 19:09 — Daniel, Sep 13 '13 at 19:09
Are you open for an Excel formula? :) – Siddharth Rout Sep 13 '13 at 20:24 — Siddharth Rout, Sep 13 '13 at 20:24

score 6 · Accepted Answer · answered Sep 13 '13 at 19:12

6

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

answered Sep 13 '13 at 19:12

Andy G

19,232
5
47
69

So if I wanted to use this in a cell, would I do something like this? =IF(YellowIt(A5) = True,"True","False")? – 1337Atreyu Sep 13 '13 at 19:14
You would write `=YellowIt(A1)`.. if you choose to ignore my advice. – Andy G Sep 13 '13 at 19:15
The formula-version in the prior comment would change the Boolean results to the strings "True" and "False". In which case, you might as well change the return values (in my function) to `As String` and "True" and "False". – Andy G Sep 13 '13 at 19:19
But if I wanted to use this as a conditional statement in an IF function, could I say =IF(YellowIt(A5)=True,Sum(A6:A7),0)? – 1337Atreyu Sep 13 '13 at 19:20
It's still returning #NAME?. I tried changing it to a string and just have it return "True" or "False", but it's giving me the same thing. – 1337Atreyu Sep 13 '13 at 19:25
And regarding the advice of putting this in the original condition, there is no condition. This is a way that we can manually change the hilighted color and change the condition of the formula. – 1337Atreyu Sep 13 '13 at 19:27
I'm using Excel 2010. If you copy and paste the entire function into a Standard Module it will work. If not, there must be something else wrong. In which case try it in a new, blank, workbook. – Andy G Sep 13 '13 at 19:29
Magically, it just worked. I made absolutely no change and it suddenly decided to work. Curious. Anyway, thanks for your help, it was invaluable! – 1337Atreyu Sep 13 '13 at 19:41
FYI: It is possible to achieve it with Excel Formula – Siddharth Rout Sep 13 '13 at 20:24
1

Sure. See this [link](http://stackoverflow.com/questions/15887257/how-to-count-up-text-of-a-different-font-colour-in-excel). Here I am determining the Font Color. Similarly you can do it for cell's interior color. The `type_num` for interior color becomes 63 if I am not wrong. If I remember correctly, I answered a question for interior color as well... Now I don't remember which forum it was though :) – Siddharth Rout Sep 13 '13 at 20:52
I couldn't find my post but I found JKP has also covered it [HERE](http://www.jkp-ads.com/articles/excelnames08.asp) And yes it is 63 :p – Siddharth Rout Sep 13 '13 at 20:55
@SiddharthRout Interesting that this is possible, +1 thank you. But I think I'll stick with my original advice. Personally, I'd rather not use a formula (or xl4Macro). Others can ;) – Andy G Sep 13 '13 at 21:01
vba over formula or formulas over vba is definitely a personal choice :) – Siddharth Rout Sep 13 '13 at 21:03

score 3 · Answer 2 · edited Mar 25 '16 at 18:44

3

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)

edited Mar 25 '16 at 18:44

Dani Aya

133
8

answered Sep 13 '13 at 19:11

Joe

6,767
1
16
29

1

I tried this as well and it is still is just returning #NAME? – 1337Atreyu Sep 13 '13 at 19:28
It worked for me -- though the cells don't automatically refresh. If you created the cell formula first, you'd have to go back into it and hit enter again. – Joe Sep 13 '13 at 19:48
1

+1 Another way to do it :) And for Automatic Refresh, you can use `Application.Volatile` in your function :) – Siddharth Rout Sep 13 '13 at 21:00

score 2 · Answer 3 · answered Jan 27 '15 at 09:27

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

score 1 · Answer 4 · answered Mar 05 '15 at 17:20

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.

score 0 · Answer 5 · answered Jul 23 '15 at 13:03

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)," ")

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

5 Answers5