Creating table with conditional formatting based on growing contents

Hey Guys!

Im trying to create some schedules for where I work. It incorporates colleague's working hours and measures it against the required labour. For example, if I need 50 hours of labour, but only have 40 hours worth of colleagues, I will need to gather 10 hours. Shifts are in 4 hour slots, so I would require 3 extra colleagues to cover the remaining 10 hours.

Ive got a basic table setup where the Title is "x Extra Required". The X is just a number with formatting adding "extra required" on the end. I have conditional formatting in place so that if for example I need 2 extra shifts, the boxes become un-highlighted. However, as you can see in the picture, I need 2 extra shifts, even with Colleauge 1 taking up one of them.

b8qvpg.png

With the use of conditional formatting, i need to get a third cell 'un-highlighted' based on the top "2 extra required" cell. I need it to work in all cells dependant on how many colleagues have already signed up and how many more are required.

This must be done in Conditional formatting and no VBA please. Ive been racking my brains for a few hours now and cant come up with anything. If anyone could give me a hand, it would be so much appriciated!

Cheers

PS - Can only be done with Excel 2003 please, thats the latest software available to me.