Worksheet On Change Calls Sub to change Cell colors

**sportsguy** · 02-01-2014, 11:05 AM

I sent out large transactions files with filters for each office location to filter on their location.
Often times I turn filters on check pieces of information myself. Most times, I forge to turn them off before sending
The receipients then assume the filters are not on, and email me back asking where their location's data is. . .

So, the bulk of the change on the worksheet is changing the filter settings.

I have written a sub which goes through the filter row to highlight colors if any of the filters are turned on.

to find them very easily for me. . . to keep my customers happy. .

But the code isn't working quite right yet

What am i missing??

Please Login or Register  to view this content.

in module

Please Login or Register  to view this content.

thanks in advance!
sportsguy

**MarvinP** · 02-01-2014, 12:20 PM

Hi SportsGuy

Event Code is like black magic in the VBA world. First you need to have it in the Sheet module, which it looks like you do. Then you need to key on it somehow, using the correct event type. I like to use the OnDoubleClick but you need the OnChange (maybe). My question is this: If you select a different filter in C9 does the data in cell C9 really change? If it doesn't change then the OnChange code won't trigger.

After doing some reading it looks like there is a newer (since Excel 2007) code to trigger on TABLES. Do some stud with the two links below and things will get clearer. You should also be putting breaks in your code and stepping through it a line at a time to see what happens.

http://www.cpearson.com/excel/Events.aspx
http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

Your question of "What am I missing?" isn't an easy one to answer. I think you just need to learn more about event code and how tables deal with it.