Converting formula to a code

Try this version of your workbook

1/. I have added a sheet "Lookups" and created dynamic named lists for each catagory you require for data validation, you can add /change/ delete any data in these lists.
Don't leave blank cells in these lists, if you want to delete an item use Delete Cells > Shift Cells Up

This makes the code in the worksheet change event much simpler.

This Lookup sheet can be hidden.

2/. I have changed you macro "Test" to "TestStatus" and moved it's trigger to the ThisWorkbook module, Workbook_Open event, and also called it in the Worksheet_Change event (Sheets("Status"))

I have assumed that each project will have an SI number and the last row in this column ("A") determines the number of rows to check the status as changes are made.

3/, 2003 can only handle three conditional formats, I have changed the base colour of Column J to allow the colour codes you require

Try changing the data in Column E to test the drop-Down in F

Try changing dates in columns H K & L to se the result in column I, you probably don't need the validation in column I, but I have left it just in case.

Hope this helps