I'm fairly new to Excel and struggling with what I thought would be a simple formula.

I have a tracking sheet with three levels of task urgency: Green, Yellow, and Red. I have an overview sheet containing a "highest urgency" for the tasks contained in several sheets. So if "sheet b" has a green and a red task, it will show red for "sheet b" on the overview.

I want Excel to search for these colors (in the form of strings, i.e. the color name typed in the cell) on each sheet and highlight the respective overview sheet cell in the highest urgency among the three. For the life of me, I'm clueless, is this even possible with Excel's conditional formatting rules? I've been trying =SEARCH formulas but I can't even get it to find "red" and highlight my cell red, let alone prioritize red over green.