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

 

[NARAYANK991]

Hi ,

While I think about it , why not go about it another way ?

As I understand you , you wish to know whether a particular column in a table has been filtered on the colour RGB(255 , 199 , 206) ; is this correct ?

If this is the case , can we not just loop through the visible cells and check for whether even one cell has this interior colour ; if yes , and if a filter is active on the column , it follows that this colour has to be among the criteria.

Is this correct ?

 

[Klash Ville]

Hi ,

While I think about it , why not go about it another way ?

As I understand you , you wish to know whether a particular column in a table has been filtered on the colour RGB(255 , 199 , 206) ; is this correct ?

If this is the case , can we not just loop through the visible cells and check for whether even one cell has this interior colour ; if yes , and if a filter is active on the column , it follows that this colour has to be among the criteria.

Is this correct ?

That is correct, even though loops are not efficient, it's probably the only solution right now.

Could you give me a code suggestion? Also note that this RGB color is originated from Conditional Formating, which has a different behaviour.

But anyways, thank you for all your help so far

 

[NARAYANK991]

Hi ,

In that case , even this approach is ruled out , since Conditionally Formatted colors are more difficult to identify.

In case I can think of something , I will post it.

 

[NARAYANK991]

Hi ,

See if this works :

If ActiveSheet.ListObjects("Table2").ListColumns("Column1").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(1).DisplayFormat.Interior.Color = RGB(255, 199 , 206) Then

 

[Klash Ville]

Hi ,

See if this works :

If ActiveSheet.ListObjects("Table2").ListColumns("Column1").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(1).DisplayFormat.Interior.Color = RGB(255, 199 , 206) Then

Hello my friend, sorry for the delay.

I will try this right now and give you feedback right away.

 

[Klash Ville]

Hi ,

See if this works :

If ActiveSheet.ListObjects("Table2").ListColumns("Column1").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(1).DisplayFormat.Interior.Color = RGB(255, 199 , 206) Then

Yes! This works flawlessy, thank you so much!

Thank you so much in providing me the solution for this major obstacle I had, it works absolutely well, and it recgonizes my conditional formated cells. It's perfect!

Thank you =)

 

[Klash Ville]

This is the final working code:

If ActiveSheet.ListObjects("Table2").ListColumns("Column1").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(1).DisplayFormat.Interior.Color = RGB(255, 199, 206) Then
            MsgBox "Filter has already been applied"
        Else           
            With ActiveSheet.ListObjects("Table2")
                .Range.AutoFilter field:=.ListColumns("Column1").Index, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
            End With
        End If