Check if Blue

Melanie,

Apply as criteria the condtion that you use to make a cell blue.

Hope this helps.

Aladin

=======

I wasn't clear

I tried that, but my conditional formatting criteria is a little complicated. I have a tolerance table. Different conditional formats are applied all over the "database" based on what's in that Tolerance table. The end result is always a yellow background, but the way to get there is always different. Specifically, there is more than one type of formatting in any one column so I can't apply the same AdvancedFilter criteria all the way down unless I just look for the same final results.

Is there some sort of formula like BackGroundColor(A6)?

Thanks!

Maybe...

you can use the following user-defined function, one that every VBA-beginner seems to get to know,

Function FILLCOLOR(cell) As Variant
Application.Volatile True
FILLCOLOR = cell.Range("A1").Interior.ColorIndex
End Function

Aladin I tried that, but my conditional formatting criteria is a little complicated. I have a tolerance table. Different conditional formats are applied all over the "database" based on what's in that Tolerance table. The end result is always a yellow background, but the way to get there is always different. Specifically, there is more than one type of formatting in any one column so I can't apply the same AdvancedFilter criteria all the way down unless I just look for the same final results. Is there some sort of formula like BackGroundColor(A6)? : Melanie, : Apply as criteria the condtion that you use to make a cell blue. : Hope this helps. : Aladin : ======= :

I had a similar idea, but it's not working......

I had a similar idea and wrote the following function:

Function IsHighlighted(TheCell As Range) As Boolean
TheCell.Select
If TheCell.Interior.ColorIndex = 6 Then
IsHighlighted = True
End If
End Function

It works fine if I just reference it in a cell. It returns the right value. Two problems, though:

If I change the fill of the cell it's checking, it doesn't change the value.

And, if I use it as a condition in my Advanced Filter Criteria (=IsHighlighted(C2)), I get an error that says "Unable to get the Interior Property of the Range Class" I don't have any blank cells as help suggests. When I quit, it puts #VALUE for all of my criteria and I get nothing in my filter.

Thanks for you help! you can use the following user-defined function, one that every VBA-beginner seems to get to know, Function FILLCOLOR(cell) As Variant

I just tried your function and I get the same error

I tried your function instead of mine and I get the same error when I use it in my Advanced Filter Criteria. you can use the following user-defined function, one that every VBA-beginner seems to get to know, Function FILLCOLOR(cell) As Variant

Second maybe...

Just an idea: Why not add another column to your data where you apply your UDF and cover that column in your criteria?

Aladin

======== I had a similar idea and wrote the following function: Function IsHighlighted(TheCell As Range) As Boolean

A method without UDF/VBA .......

If you are going to use a UDF, then you might just as well use a macro (possibly event-triggered) so that you don't have to add another column.

If you are going to add another column, then it can be done without UDF/VBA (and with the AutoFilter instead of the Advanced Filter) :-

1. Assumption : the extra column will be inserted to the right of the column being filtered
2. Define a Name (let's say Color) and in the refers to box put: =GET.CELL(38,INDIRECT("rc[-1]",FALSE))
3. In the extra column in as many rows as required, enter the formula =Color. This will produce the ColorIndex number of the cells in the column to be filtered
4. Auto-Filter by the extra column (by whatever ColorIndex number you wish)

Alternatively, if the relevant ColorIndex number is 5(for example), in step 3 you could use the formula =Color=5, and then in step 4 use TRUE as the filter criterium.

There is a drawback to this method.
When there is any color change, the formula in the extra column does not up-date automatically - it is necessary to re-enter the formula.

It may be possible to use this method via the Advanced-Filter so as to avoid adding an extra column, but I can't work out a way to do it.
Just an idea: Why not add another column to your data where you apply your UDF and cover that column in your criteria? = : I had a similar idea and wrote the following function