Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do i auto-update an Excel auto-filter (filter by colour) when the colour of cells is changed?

Use case: I change the colour of one cell to the colour that was filtered. I want to see the current row disappearing without having to do anything else.

i was able to use VB given for the value-change-row-disappearing case from: How to I auto-refresh an Excel auto-filter when data is changed?

but don't know how to make it work for colour case. tnx.

share|improve this question

You would need an event to track the color changing of a cell.
Yet, there is no such event in Excel VBA.

The only way to use an event would be to use: Worksheet_SelectionChange.

So the code would be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'code described in the other question

But this is probably overkill as it will refresh your filter every time you select a new cell.

share|improve this answer
Could we track .colorindex changing? – Raystafarian Feb 9 '12 at 11:37
No you can't track it. You can track when the Value is changing only – JMax Feb 9 '12 at 11:56
Don't take me as challenging you, just trying to learn. What if we set a var for the colorindex number and tracked that value of it changing? Or are you saying the only thing to track changing is cell.value? – Raystafarian Feb 9 '12 at 12:02
Sorry, I wasn't clear enough in my answer. You can find here some information about which events you can track. This will explain how you can use Worksheet_Change to track when the value of the cell is changing whereas Worksheet_SelectionChange will track the Selection change. You cannot store the value of the color index of every cell of the worskheet so I don't think that would work. I hope this is clearer :) – JMax Feb 9 '12 at 12:14
Pulling out the soapbox for a second, color is NOT data. In every instance where I've helped users with their spreadsheets using conditional formatting with an in-cell drop down solved their problems and gave proper data to sort without relying on color as data. – Jesse Feb 9 '12 at 23:04

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.