how to autopopulate a cell based on values in other cells following some specific rules

Hello,
it seems my need cannot be covered by simple formula so hope VBA can help.
I have row where I would like to show (autopopulate) the actual status (planned, delay, finish1, finish2) of a milestone based on status.
of several steps that belong to that milestone. Steps status (populated manually) are just below the cell of milestone status.

Rules are the folllowing:
1. if all the steps are marked as empty status shoud be blank
2. if all the steps are marked as planned milestone status shoud be "planned"
3. if even one of the step is marked as "delay" milestone status shoud be "delay"
4. if we do not have empty, planned, delay but just "finished1" and "finish2" milestone status shoud be "finished1"
5. if we do not have empty, planned, delay and finish 1 and all steps values are "finish2" milestone status shoud be "finished2".


I would like to mention that in the same column (representing a week) we might have seceral milestones and many steps.
Status of milestone1 should be formed based on steps that belong milestone1.
Status of milestone2 should be formed based on steps that belong milestone2.

Hope if sth is not clear the example file attached can explain.
If simple formula can help - that solution would be more prefarable.

Thank you.

Hi PCI,
I added the code in the xls sheet but it does not work.
It seems I do not know how to introduce the code.
Can you help on that?

Ok see your file attached

With some formulas just to start you could do:
amount of empty cells =COUNTIF(C4:C10,"")
amount of cells with "d" =COUNTIF(C4:C10,"d")
amount of cells =COUNTA(D4:D10)+COUNTBLANK(D4:D10)
then see with some IF functions