Search multiple rows for cell color, return date from same column

Hi there, I'm hoping to get some guidance...

Management at my workplace have a Gantt-style spreadsheet for scheduling production. The top row has dates and the left column lists job numbers. Most job numbers repeat on 3 to 5 rows. Departments are identified by cell color. (The values in the colored cells are units of time.)

Based on a list of job numbers and cell colors, I need formulas that would look up the left-most and right-most values for each cell color (department) from all the rows with that job number, and return the dates from the top row in the same columns as those values.

I have a module for the ColorIndex UDF, but I'm not sure how to use it in combination with the other functions needed. Any advice?