I'm not an excel expert but perhpas you could take a different approach to
solving the problem.

If you organize the data with employee, week no, and hours worked as the
columns the conditional formating function should be straight forward using a
conditional sum and the results are returned correctly if you sort the data by
employee and then week no.

Then its just a matter of adding a flag column and creating another table the
reports it in the fashion you choose (employee names as column headers and week
numbers as rows), no programming is neccessary.

For instance if I add a flag to each row using the table structure suggested
above, the reporting format can be determined by using a lookup function or
database function for the last row of the employee name and reading its flag
value (green, yellow, red).