How to change the color of the cell that contains the largest value to a spec. color?

[George59]

Hi there,

A sub macro generates numbers in the range of E5:Q7 (3 rows, 13 columns), where I would like to have highest valued cell in each column to be highlighted (background) with different color, the color depends on which row the cell is located in.

If the highest valued cell in each column is located in the 1st row, that cell would become green (4), if a cell in the 2nd row would turn up the largest value, then it would become red (3) and the 3rd row would generate yellow background color (6).

So, basically the largest valued cell need to be found in each column of 3 cells and assign the designated color to that row.

For example: (this forum allows to change the color of the font only, not the background)

4 8 5 9
1 2 7 4
9 4 3 7

Any idea, how should I go about it?

[DBY]

Hello
Try the following conditional formatting formula in cells E5:Q7:

Please Login or Register  to view this content.

Amend the row = to 6 and 7 for each rule and assign colour.

Just an added thought, but as I believe you're using Excel 2003 and you may need further conditional formatting on those cells, instead of using up all 3 options, as my first example formula does, you could apply the formatting to each individual line, thus leaving you 2 further options for those lines. As follows:

Please Login or Register  to view this content.

Adjusting the formula for rows 6 & 7, E6; E7 etc.

Hope this helps.
DBY

[George59]

Hi DBY,

Currently this is what I have in cell E5 (and similar code in the remaining cells):

=SUMIF(E9:E47;"=Yes";$D$9:$D$47)

So, how would you go about combining your code with this one?

George

[DBY]

Hi
Sorry I'm not too clear exactly what your Sumif formula is doing in those cells. I thought the values were generated by a Macro.

A sub macro generates numbers in the range of E5:Q7 (3 rows, 13 columns), where I would like to have highest valued cell in each column to be highlighted (background) with different color, the color depends on which row the cell is located in.

Could you explain further, or perhaps upload a sample file?

DBY

[George59]

I attached a screenshot of this vote counter, actually it is ready, I am just trying to add some whistles and bells and giving a color to the cell with the vote result in each subject matter is what I am working on right now.

excel.jpg

Actually how this vote counter works is that each apartment owner is represented by the percentage of the ownership of the whole apartment building. In this case the apartment building itself is equivalent with 10000 %, and for example the apartment "A/1" has 354 %. The total number of apartments and garages add up to 10000, in our case it is 27 apartments and 12 garages. I forgot to mention that here we're talking about square footage of each unit, in our case it is measured in square meters.

Anyway, the range of cells in question display the results of the votes for each subject matter that the owners voted on, in the example on the screenshot the no. 1. subject that was voted on PASSED (Igen), the no. 2 subject was voted down, it didn't pass.

=SUMIF(E9:E47;"=Igen";$D$9:$D$47)

What this expression does is, it wil count the Yes (Igen) votes in the cell range of (E9:E47), but instead simply counting the Yes votes, it sums up the square footage areas of all the voters who voted Yes (Igen).

Same applies to the No (Nem) and Abstain (Tartózkodik) votes.

I hope, I managed to clarify the usage and purpose of this expression:

=SUMIF(E9:E47;"=Igen";$D$9:$D$47)

George

[DBY]

Thanks for clarifying. The conditional formatting formulas are not entered into the cells as with the Sumif function you're using. Assuming you still need to conditionally format cells E5:Q7, then you need to select the range of cells and go in to Format > Conditional Formatting and select 'Formula Is' from the dropdown and place the formula in the dialogue box then choose your format.

You can either select the whole range E5:Q7 and use my first suggestion, or select each row individually and use my second suggestion, if you should need more conditional formatting.

DBY

[George59]

I did it for the whole range, I got an error, that there is an error in the formula:

excel2.jpg

So, to recap, this formula would leave all numbers in place and select or make active the cell that I would want the condition to color if it contains the largest value?

[George59]

I got it. This is what I used in the conditional formatting in the cells of the 1st column:
=E5=MAX(E$5:E$7)
=E6=MAX(E$5:E$7)
=E7=MAX(E$5:E$7)

and copied this to all 13 columns.

DBY, thanks for all your help!

George

[DBY]

Glad it was solved George.
With the first example you did not need to change the E5 references for Conditions 2 and 3 to E6 and E7. If you selected the whole range, then as they're relative references, apart from the Max rows, Excel would do that for you. Though I'm not sure why you were getting a formula error message. But as the other way worked then it's academic.

Regards DBY