Filtering Macro Please help!

[paquirl]

i need a macro that will do what i am doing manually:

1. i set an autofilter.
2. i then filter results by the "Weeks Late" column for anything greater than or equal to 5 to 6 weeks. i mark the results by highlighting and change column filter back to show all results for that column.
3. then i filter by the "Mat'l Status" column for anything status 45 or 55. i mark the results and change column filter back to show all.
4. then i filter by "Sufficient Inventory?" column for anything with "yes." i mark the results and change filter back to show all.
5. finally i show all the marked results which is what i want in the end.

can anyone please help?

 

[texasalynn]

Try using the macro recorder. Then after you record the macro you can edit it to make it work generically. Post back with questions for the editing.

 

[Trouttrap2]

Are you trying to highlight entire rows that meet your conditions or just individual cells?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
You could use the Conditional Format tool instead of a macro. If using Excel 2003, highlight the range you want then use CTRL+O+D or Format>Conditional Formatting. Add your conditions and formatting. Doing this you won’t have to filter and unfilter.
<o> </o>
If you’re set on filtering, you can record a macro using the Record Macro built in tool. Just record your steps you’ve outlined. Tools>Macro>Record New Macro.

 

[paquirl]

the conditional formatting thing looks cool. but if you dont use a macro, how do you automate it?

 

[Trouttrap2]

The conditional format tool is always on for the range it applies. So if you have a "5" or "6" in the field it will be highlited automatically. Every time you add data it will find and highlite according to the conditions you specify. You won't be able to remove the highlite unless you change the Conditional Format.

If you want to be able to remove the formatting, turn on and off, then a Record Macro might be best.

 

[paquirl]

i've tried the macro recorder multiple times, but something always goes wrong. could i email you the file and show you what i'm trying to do?
-Andrew

 

[Trouttrap2]

You can paste some of our data in your reply and I can look at it there. Will that work?

 

[Trouttrap2]

1) Before you record have your AutoFilter buttons visible.
2) ALT+T+M to open your recorder. Then when setting the keys hold down SHIFT when you set your shortcut key. Better that way.
3) Use custom filter in the drop down autofilter for "Weeks Late" for anything >=5, AND <= 6. Two conditions here.
4)Follow same steps for the "mat'l Status" and "Sufficient Inventory?' columns.
5) Turn off your recorder. Usually found in a floating tool bar..
Then use your shortcut keys you set when you started the Macro.

 

[paquirl]

the problem is there is overlap between the criteria, i.e., a row that is 5-6 weeks late may also be status 55 or have "yes" in inventory. to account for this, i filter by one thing, mark the rows affected, then i turn the filter off and filter by the second thing, mark those rows, then turn it off and filter by the third thing, then turn it off.

 

[Trouttrap2]

Gotcha….so what you are saying is if any of the conditions are true the whole row gets highlighted. That means if it is 3 weeks, but a “yes”, that row will be highlighted.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
If I understand that correctly it should not matter with a recorded macro. When you are recording, just highlight all the rows that show up after filtering – even if they were highlighted earlier because of meeting one of the conditions.