Target cell coloured based on whether one cell has a greater value

[Guest]

I have a table with 4 statuses e.g. green, amber, red and blue.

I have a target cell which I want to be coloured according to which colour
is the predominant one.

So, if the value under 'green' is greater than the value for 'amber', 'red'
or 'blue', I want the target cell to be coloured green. Likewise, if the
value under 'amber' is greater than the value for 'green', 'red' or 'blue', I
want the target cell to be coloured amber.

So far, I have tried conditional formatting, but no joy. Any advice
gratefully received.

 

[Guest]

I am not going to do it all for you, but conditional formatting works.

The conditional format in your target cell should have 3 conditions.
You will need to use
FORMULA IS =A1=MAX(A11), colour 1
FORMULA IS =B1=MAX(A11), colour 2
FORMULA IS =C1=MAX(A11), colour 3

and the other colour (if D1 is the greatest) will be the default normal
colour that you set for the cell.

Conditional formatting only offers you 3 colours, so if you need more than
that you will need some programming help.

 

[Guest]

Thanks Alllen. This does solve about 80% of it. However, I do need to do this
for 4 colours, so I guess I need to investigate worksheet_change() or
worksheet_calculate() event macros.