Macro that checks a list, and gives user prompt for action

Hello Everyone!

I have used this site in the past and found the advice very helpful so I thought I would register. To the point where I am now starting to take on some bigger excel VBA challanges, actually.

I have provided a link to download an example workbook... this one is a huge challange and I'm not even sure where to start!

On the example workbook there is a base data table, it describes the movement of items through "phases". To track this progress I use another table, which is actually in a different spreadsheet altogether but on the example it is shown on the table tab.


The basic idea is that an item arrives, at phase 1. It either passes through phases 2 and 3 in order, or goes straight from phase 1 to 3 before going to phase 4 which is date stamped. At that point, the item would disappear from the data table as that one doesn't show phase 4. At each step the quantity may change slightly (the reduction or addition is random).

So here is the challenge; unlike in the example there are thousands of rows of data to be checked. The way i check that items are matching each other is to pivot table the Data and then manually go down the list and mark off any changes on the second table.

I have included pretty much all of the possible movements in the example book. (1-3,1-2,new arrival to phase 1,item 5 is showing that half of the quantity has been moved to phase 4 (with a date stamp sample, there are thousands of items to check each day - comparing the pivot table with the table tab and making changes to the quantities by overtyping the cells. So, what i would like to do is make a macro that would do many things really, i suppose the first step would be for someone to get me started by telling me if it's even possible.

A macro would:

- Check off items and quantites with no change, perhaps turning the text on the table tab a certain colour once it has successfully matched.

- Bring up a dialog box of some sort (I do actually have an example as a userform in the example workbook). The dialog box would be asking the user to confirm the action that is needed for each item that has moved, according to which phase it's moving from. In every case, the action for the selection would be just to confirm that the quantity is to move to the reported phase column.

* I would like the dialog box readout (or whatever the best solution ends up being), to display the query as, for example, "Item 1 moves from Phase 1 to Phase 2, quantity +200"
To which the user would either click confirm and the table is adjusted to reflect that move, or cancels and the row is highlighted for review later.

* In the case of a move to phase 4, would the macro be able to use the date stamp and assign a kind of rule of "if the date stamp = yesterday then make the change without asking, otherwise ask"?

OK, i hope i haven't scared everyone away as this, to me, is massively ambitious. I'm going to be keeping an eye on this thread myself while i work on the problem so i'm looking forward to being told that "it's crazy, but it's crazy enough to work"!

If I haven't been super clear, please don't hesitate to ask for clarity.




http://www.mediafire.com/download/2c...Challenge.xlsm