Need an if statement to find the color of a cell

November 29, 2012 at 06:16:26
Specs: Windows XP

Just like the feature to filter for color.. can we also do this in an IF statement in a cell?

See More: Need an if statement to find the color of a cell

Report •


#1
November 29, 2012 at 07:17:46

AFAIK it can't be done with any built-in Excel function, but it could probably be done with VBA. However, it might be very cumbersome in any version later than 2003.

In 2003 and earlier, Excel had a 56 fill colors to chose from, so it was fairly simply to find a color via VBA by using the ColorIndex number.

In 2007 and later, you have to deal with ThemeColors, TintAndShade, etc.

For example, in 2003, here's what the macro recorder produces when you fill a cell with Yellow:

Sub Macro2()
    With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
End Sub

To find a cell that was filled with Yellow, you simply have to check for ColorIndex value of 36.

In 2010, here's what you get when you choose Yellow from the standard colors:

Sub Macro2()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Here's what you get if you choose one of the Theme colors, one of the Orange shades in this case:

Sub Macro3()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
End Sub

In order to find that cell with VBA, you would have to check the cells for all of those attributes and get them exactly right.

It's a real PITB.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 29, 2012 at 08:04:29

For a round-a-bout way where you can use an =IF() function,
but requires the use of a Defined Name and XLM4 Macro functions, see Reply # 4

http://www.ozgrid.com/forum/showthr...

MIKE

http://www.skeptic.com/


Report •

#3
November 29, 2012 at 10:03:37

Mike,

Have you tried that method in 2010 with some of the ThemeColors?

Different shades of the colors will give you the same ColorIndex number even if the cells aren't really the same "color".

That's because 2010 adds Shadeing and Tinting to the standard colors so there is more involved with the filling of the cells than just the ColorIndex value of days past.

That method may work in some cases, but it won't work for every color available in the new pallete.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 29, 2012 at 11:48:31

Have you tried that method in 2010

I have 2007, but thought if might be worth a shot.

MIKE

http://www.skeptic.com/


Report •

#5
November 29, 2012 at 18:22:59


I colored a number of cells in Row 1 with a series of ThemeColors from the ThemeColors palette. I used colors from single columns, actually 2 columns worth.

When I dragged the =color function along Row 2, some of the colors returned the same ColorIndex numbers, but because of the ThemeColors and other attributes of the new color palette, the colors used in the different cells were not the same.

I've been trying different VBA solutions to deal with colors now that my office has upgraded to 2010 and it's a real pain. A lot of macros that I've been using for years have to be modified and in some cases I haven't found workable solutions yet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
November 30, 2012 at 09:37:46

You have given some real neat stuff to think about.. The solution I was seeking was simple, since I was looking to share a single albeit, complicated formula for my team to paste in and then filter on. I will just add a few add'l steps to make sure we handle the filter by color manually, but I do look forward to playing with this in the back end soon.

Thanks all for the insight.


Report •


Ask Question