Sure,
Okay, here goes the explanation. I use your original macro from the first post several times, each in it's own module. You may ask "why not put them in the same module? Well, in the beginning I tried that but I got a "duplicate scope" error, so I created another "combine sub" module that calls each module and eliminated the error.
At the time, I didn't know that if you put these lines of code...
above the sub, you can have as many subs in the same module as you want to. Having said that, all future subs that have the same "Dims" will all be in the same module.
Anyway, I use your sub multiple times throughout my routine. Each time though, I change this line of code here...
to look for a different column
and change this line of code here...
to change the criteria to search for.
After all variations of the "paper" sub are run, I get a nice list that allows me to make a quick scan of compliant cells versus non-compliant cells. I know that if cells are grey, not only are they compliant, BUT...they have also been checked for compliance, they have been "looked over".
I use this sub for data integrity. If cells have no data or incorrect data, it highlights ONLY the non-compliant (errored) cells red.
So, post macro, my result is many many columns that have been turned from white to grey, with a few "red" cells along the way.
After all the variations of the subs have been run, I then have a 90%+ worksheet of grey cells.
I then said to myself, how can I delete any rows that don't have even one RED cell in them? So I came up with the idea to create another column named "RED". The "RED" macro puts a "YES" in the "RED" column anytime a cell that's highlighted RED is found in that row and then delete any rows that don't have "Yes" in the "RED" column.
And that is what this sub here does... although, I'm still not 100% confident that the portion of code (done by the macro recorder) highlighted in blue is "bug proof" for 2 reasons
1. I'm not sure of the relevance of the "X5" reference
2. Not sure if the search STOPS when it's equal to the last data row in the "Customer Number" column because it seems to take a long time to run even if I have, let's say just 5 errors.
I hope that explanation was clear, anymore questions, please ask.
Thanks!
Bookmarks