Loop through rows and generate new records

First of all - great site. I have been reading through the forum for quite some time and have found many threads quite valuable. But it is time to create a thread for my own since I have not been able to find a discussion for my question.

I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another worksheet.

I have one worksheet with the following field and data:

ItemId....Red....Yellow....Green....Multiple....Inventory position

0001.... 10.... 30.... 50.... 5.... 45

0002.... 5.... 40.... 47.... 5.... 23

0003.... 11.... 20.... 30.... 10.... 5

I would like to generate new rows (in another worksheet) based on the above fields and three rows.

Basically the end result should look as the following:

ItemId....Qty....Start inv....Aggregated inventory....Prioritization

0002.... 5.... 23.... 28.... Yellow

0002.... 5.... 28.... 33.... Yellow

0002.... 5.... 33.... 38.... Yellow

0002.... 5.... 38.... 43.... Green

0002.... 5.... 43.... 48.... Green

0003.... 10.... 5.... 15.... Red

0003.... 10.... 15.... 25.... Yellow

0003.... 10.... 25.... 35.... Green

The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in worksheet 2) is above the green value.

The priotization value should be based on the start inv (in ws 2) compared to the values in red, yellow and green in ws 1.

It would be much valuable if someone could guide me in the right direction towards an appropriate VBA design. I have found some code online which could solve the frame structure but I am looking for what to place into the middle of the code section.

Sub Test2()
      ' Select cell A2, *first line of data*.
      Range("A2").Select
      ' Set Do loop to stop when an empty cell is reached.
      Do Until IsEmpty(ActiveCell)
         ' Insert your code here.
         ' Step down 1 row from present location.
         ActiveCell.Offset(1, 0).Select
      Loop
   End Sub

I am looking forward some great answers.

Much appreciated - thank you in advance.