How do I detect if a certain table column has a certain filter activated?

[Klash Ville]

Hello everyone,

I have a rather complex question... which is the following:

I have a table column header called "Column1" which has the following filter activated through VBA:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=.ListColumns("Column1").Index, Criteria1:=RGB(255, 199, 206),
Operator:=xlFilterCellColor

This filters for all cells whose color result from the following RGB code combination which was inicially formed through Conditional Formatting. Anyway, I cannot figure out the right way to build an if to detect either if that filter is being activated, or not.

Basicly, I want this:

If Column1.autofilter = RGB(255, 199, 206) Then
MsgBox "Yes"
Else
MsgBox "No"
End If

 

[Klash Ville]

BUMP please help...

 

[NARAYANK991]

Hi ,

Have you checked this to see if it can be applied to your case ?

https://www.mrexcel.com/forum/excel-questions/135538-posible-vba-find-out-column-filtered.html

 

[Klash Ville]

Hi ,

Have you checked this to see if it can be applied to your case ?

https://www.mrexcel.com/forum/excel-questions/135538-posible-vba-find-out-column-filtered.html

I'm looking at it now, I have tried to follow their sugestions but I was unsuccessful...

could you help me?

 

[Klash Ville]

The thing is I don't need a for... and I don't need to know if filters are ON or OFF.

I simply wish to know how to to know if a certain column has a certain spefici filter activated like the following code I provided above

If ActiveSheet.ListObjects("Table2").Range.AutoFilter.Criteria1 = RGB(255, 199, 206) Then
        MsgBox "Filter mode is on"
    Else
        MsgBox "Filter mode is off"
    End If
End If

This is the closest I got to success, but, it complains about an object not being present... I don't know what else to do... help pls

 

[Klash Ville]

The thing is I don't need a for... and I don't need to know if filters are ON or OFF.

I simply wish to know how to to know if a certain column has a certain spefici filter activated like the following code I provided above

If ActiveSheet.ListObjects("Table2").Range.AutoFilter.Criteria1 = RGB(255, 199, 206) Then
        MsgBox "Filter mode is on"
    Else
        MsgBox "Filter mode is off"
    End If
End If

This is the closest I got to success, but, it complains about an object not being present... I don't know what else to do... help pls

Someone?

 

[NARAYANK991]

Hi ,

I am not sure , but see if this works :

If ActiveSheet.ListObjects("Table2").AutoFilter.Range.Cells(2, 2).Value [COLOR=#333333][I]= RGB(255, 199, 206)[/I][/COLOR]

 

[Klash Ville]

Hi ,

I am not sure , but see if this works :

If ActiveSheet.ListObjects("Table2").AutoFilter.Range.Cells(2, 2).Value [COLOR=#333333][I]= RGB(255, 199, 206)[/I][/COLOR]

Awwww that was so close x.x
Sadly, when applied in an if with a Yes and No MsgBox, it's always stating that the filter is never applied, even when it is =/

 

[NARAYANK991]

Hi ,

Can you provide a link to your file ?

 

[Klash Ville]

Hi ,

Can you provide a link to your file ?

Sadly I cannot, but the info I provided is just enough to get the entire picture.

In 1 table column, I got some cells with the RBG color that was originated from Conditional Formatting, and then I filter those same cells with the following code:

           With ActiveSheet.ListObjects("Table2")
                .Range.AutoFilter Field:=.ListColumns("Column1").Index, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
           End With

And this is your code applied with my filter code above:

        If ActiveSheet.ListObjects("Table2").AutoFilter.Range.Cells(2, 2).value = RGB(255, 199, 206) Then
            MsgBox "Yes"
        Else
            MsgBox "No"
            With ActiveSheet.ListObjects("Table2")
                .Range.AutoFilter Field:=.ListColumns("Column1").Index, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
            End With
        End If

I also just modified the cell coordinates to the correct one: (2, 8), but the result is the same... strange =/