up vote 1 down vote favorite
share [g+] share [fb]

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.

link|improve this question
feedback

1 Answer

up vote 1 down vote

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.

link|improve this answer
Could we track .colorindex changing? – Raystafarian Feb 9 at 11:37
No you can't track it. You can track when the Value is changing only – JMax Feb 9 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 at 12:02
2  
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 at 12:14
1  
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 at 23:04
show 1 more comment
feedback

Your Answer

 
or
required, but never shown
discard

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