Format Cell Color - Depending on the color or other cells (RAG status)

[gusphol]

Hi All,

I've had a quick search and I have not returned any results for this.

My issue is a simple one, but I cannot find a way to do it with conditional formatting in excel 2010. (not that I'm an expert!) I have a number of cells which I have conditionally formatted to be green, amber or red depending on the values in the cells. I now want to have another row which gives the 'overall status' for these cells. eg. if all the cells are green - the overall status will be green, if all the cells are red - the overall status will be red. Whichever cell color is most prominent will determine the overall status cells color.


If you know this your help would be greatly appreciated?

 

[CMAnslow]

Hi,

you would probably need another column and use a sumif.

For example you had a sheet with the following on :-

A B
1 A 10
2 A -10
3 B 0
4 B 10
5 B -20

With the conditional formatting on column B, I would create a column C with the following sumif formula:-

=SUMIF($B$2:$B$5,B2,$C$2:$C$5)

A B C
1 A 10 0
2 A -10 0
3 B 0 -10
4 B 10 -10
5 B -20 -10

And then conditionally format column C.

Hope that helps.