Help needed to Conditionally Format one cell based on values in a range

[ANTHT01]

How can I conditionally format a cell say 'amber' based on the occurrences of same colour "amber " in a range. For ease I have given a value for Amber = 2.I have 2 other colours with values Green =1 & Purple = 6 . So above each range there's a blank cell & the condition are :

• All in range are Amber (2) then empty cell above range is Amber
• All in range are Green (1) then empty cell above range is Green
• All in range are purple(6) then empty cell above is Purple
• If there is a mix of the colours in the range then the default colour of the empty cell is Amber
• For further clarity Green means complete, Amber means in progress and Purple means " not started"

Thanks for any assistance

 

[AliGW]

You can't do calculations based on cell colour. Do the numeric values appear in the cells? You'd need to use values to work out the average and then colour the cell accordingly. Best to attach a sample workbook.