Help Altering Code that Was Created

Hello Everybody, I have the following code created from a fellow user and I need help finishing off the coding. Here is what I'm looking for regarding this coding:

So my idea for the file numbers is a follow:
I need it to check Column I and to see if it equals the value of "Yes". If that value is found its suppose to go to the next row. It should go back to Column A and copy the file number to the cell right below it. And then rerun the previous Check to see if the Value of "Yes" is in this new rows column of I. If no value is found it will go to Column D and see if it is empty just like how you had it. If there is a value of yes then it will re run the previous section. And then it will continue on with macro. This way it only adds file numbers to the MIA's that are being used.

Also the Possible MIA Column should only equal yes if Column I has no Value and Column F's Date is reached or has passed based on the date the macro is run.

This program is basically running a check system for me. It's taking the color cells from sheet 1 and checking to see if it exists on sheet2. If it exists its going to find if it needs to add the the lasted date to that category. You can see that each number has the chance of being on the list three times. And right now the program is making it so it carrys over the information and duplicates everything until the 3 times have been fulfilled which shouldn't happen. This sounds confusing but hopefully someone can help me.

Test1b(1) (1).xlsm