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

Hello
Hope I've understood you correctly and if so, select cells H3 down to H9 and apply three conditional format formulas in the order of Red; Yellow; and Green. For example for 'Red':

Please Login or Register  to view this content.

Then apply for the other colours, changing 'Red' to 'Yellow' etc.. in the above formula. As I said, make sure in the Conditional Formatting Rules Manager they are in the correct order.

Hope this helps.
DBY

Attachment 255790

i.e, if in the range there is any entry red then the result is red, next level is if in the range there is any entry Yellow then result is Yellow and none of condition matches then result is green.

Below is formula I have used
=IF(COUNTIF(B3:G3,"Red") >= 1, "Red",IF(COUNTIF(B3:G3,"Yellow") >= 1,"Yellow","Green"))

_________________________________________________________________________________________

if you feel this has helped you, please add me to reputation.

Thank you both very much for your help - both methods work perfectly.

Very much appreciated!