changing the colour of a header cell when filtering

[lpool61]

Hi all - I've got this piece of code to change the colour of the header cell being filtered on in a spreadsheet (you click the cell to filter, and it changes colour - makes it easier for users to spot which cell is filtered - the little blue arrow isn't always obvious apparently). Anyway, the code works fine; however, it keeps firing, so the function is constantly running. This makes it practically impossible to copy data between sheets and workbooks; switching between workbooks means minimising one before maximising another. Has anyone got any ideas how to get the function to only fire when a sheet is filtered, or have an alternative to what I have already?

Please Login or Register  to view this content.

Thanks in advance

[Andy Pope]

rather than make the function volatile include an argument that is based on the data that will cause it to recalculate.

so assuming your your formula is,

=AutoFilterOn(A1)

change it to, where A1:A257 is the column of data being filtered.

=AutoFilterOn(A1,(COUNTA(A1:A257)-SUBTOTAL(103,A1:A257)))

The function will then change to

Please Login or Register  to view this content.