I have had a quick look at the worksheet with the little modification, however, it still does not match the desired results as displayed in the lower half of the worksheet "Desired Results" (rows 19 through 31).

This should also answer your first question I believe.

Am i missing something?
If i copy the data from I19:I31 to range I3:I15, then your range E3:E15 has the same values as the desired range E19:E31.(?!)
The only difference is that in E3:E15, the conditional formatting formulas for the cell colors are not completed, it has only rules 1, 2, 6 and 8 applied from the range B36:B48, the rest of rules you have to write them yourself. Open the conditional formatting, manage rules, and add the other rules...

here is again you workbook

Attached Files

Current Status Worksheet.xls (33.5 KB, 12 views)