Highlight autofiltered column headers

[mfleuette1]

Good evening - I searched this forum and did not find the answer to my question and was wondering if someone could provide help?

I am querying a SQL Server table to return query results into Excel 2010; it is returning 55+ columns.

Because there are more than 20 columns being returned, I'd like to highlight any autofiltered columns in magenta (colorindex = 7). I already have a macro to turn off all autofiltered columns...

I am pretty certain this will require listobjects(1) but so far have been unable to figure out:
a) how to determine if a particular column has an autofilter applied, and
b) how to set the colorindex for the column header

Solution outline -

Find columnheader row
i = 0
If any autofilter on
loop
i = i+1
if column(i) has autofilter on
set column header cell color to 7
else
set column header cell color to 0
end if
end loop
end if

It may be that you cannot change the color on a query's header cell (I don't know), in which case, the cell above the column header cell should have its color changed.

I can see the autofilter criteria by mousing over the autofilter arrow for a filtered column, so displaying the autofilter criteria is a "nice to have" at this point, not a "necessary". (Does anyone know how to put the filter criteria displayed during mouse-over into a cell?)

Does anyone on the board have familiar with how to check autofilters for data returned by a query?

Thank you in advance for any responses -

 

[mfleuette1]

Good evening - I searched this forum and did not find the answer to my question and was wondering if someone could provide help?

I am querying a SQL Server table to return query results into Excel 2010; it is returning 55+ columns.

Because there are more than 20 columns being returned, I'd like to highlight any autofiltered columns in magenta (colorindex = 7). I already have a macro to turn off all autofiltered columns...

I am pretty certain this will require listobjects(1) but so far have been unable to figure out:
a) how to determine if a particular column has an autofilter applied, and
b) how to set the colorindex for the column header

Solution outline -

Find columnheader row
i = 0
If any autofilter on
loop
i = i+1
if column(i) has autofilter on
set column header cell color to 7
else
set column header cell color to 0
end if
end loop
end if

It may be that you cannot change the color on a query's header cell (I don't know), in which case, the cell above the column header cell should have its color changed.

I can see the autofilter criteria by mousing over the autofilter arrow for a filtered column, so displaying the autofilter criteria is a "nice to have" at this point, not a "necessary". (Does anyone know how to put the filter criteria displayed during mouse-over into a cell?)

Does anyone on the board have familiar with how to check autofilters for data returned by a query?

Thank you in advance for any responses -

Does anyone know how to check autofilter status in Excel 2010? Thanks!