Coloring a cell based on other cell colors in row

CCC - May 22, 2016 at 03:43 AM

I am building a spreadsheet to track certifications for employees. The first column (A) shows the date that an employees information has been updated. The second column (B) has the employees name. The following columns (C thru AF) in that row show the dates of different certifications.

I have formated the cells using conditional formating to show whether any of the certifications are out of date. Red = Overdue, Orange = 60 days until overdue, Yellow = 30 days until overdue, Green = No certifications overdue.

I would like to format the cell with the technicians name (B) to change color based on the color of the other cells in that row. If there is a Red cell in the row, cell B turns red, If there is an Orange cell in the row, cell B turns Orange, IF there is a Yellow cell in the row, cell B turns Yellow, and if there is only green cells in the row, cell B turns green.

I need this to work for all of the cells in column (B) so that each technicians name (cell B) shows the status of their certifications ( the remaining cells in that row).

The sheet is set up as a table if that helps.

Also, I can use the conditions set in the conditional formating, but I need it to return a response for IF any cell in the range meets those requirements and set a hiearchy for the order if said search. i.e.) Look for over due first, then 60 days until overdue, then 30 days until overdue, then current (not overdue). Searching for color fill seemed easier.