Conditional Formatting based on Colour Priority

Hi all,

I was wondering if it was possible to prioritise colours, or at least text, to take the lowest (or most critical/important) value if it appears in a row 1st, before moving up to the next tier and checking for that in each row and so on.

In the attached spreadsheet I've made an example using Supermarket Store Stock & Store Locations. There is a key in the spreadsheet to explain the colouration, however a simple explanation is that if a store is marked as 'Green' then no stock is required - 'Yellow' would mean possible re-stock required while 'Red' is critical re-stock required.

What I would like the forumla to do, if possible, is first check the row for any Reds - if found, the 'Overall Stock Status' field would be Red. If no Reds were found, then it would check for Yellows in that row - again, if even one Yellow was found then the Overall Stock Status would be Yellow, and so on.

If anyone could help me out with this I'd really appreciate it - please let me know if you need further clarification.

Many thanks,

Tom