Change a cells colour, based on cond format cells in another range, of several colours

Hello, thank you for looking. Apologies if this has already been addressed recently; I skimmed through as many titles as I could, but I couldn't see anything that looked similar.

I am assuming that what I want (if it is even possible!) will have to be done via VBA and I am a complete novice in this respect.

I have a spreadsheet (basic example attached) which contains conditional formatting from B1:G9 to highlight the cells a certain colour, based on their value within.

I want column A for each corresponding line to colour depending on another colour in its row, with red taking precedence, then orange, then green.

I.E, if there are all/any red cells in any given row between B&G, that line's cell A should turn red. If there are no red, but only orange, or orange and green (line 4 for example), then A should turn orange. If there are only green, it turns green.

Thank you in anticipation!

What if using cond formatting only?
with column A:
1- set default color = "green"
2- for "red":
=OR(B1:G1>5)
Stick "Stop if true"
2- for "orange":
=OR(B1:G1=5)
Stick "Stop if true"

You could use similar conditions but use MAX.

For example:

Formula:

Please Login or Register  to view this content.

In CF, use separate conditions for each colour.

Please post a new sample with the date based data.

Formula in C4 (shorter):

Please Login or Register  to view this content.

Below three Cond Formating formula, in order, with "Stop If True" box is checked

Please Login or Register  to view this content.