Conditional Formatting based on Formula Result

[Arran [BMI]]

I would like assistance with some conditional formatting.

What I would like to achieve is to highlight a cell dependant on a set criteria.

Screen Shot 2017-12-15 at 13.43.14.png

As you can see from the attached I would like to highlight the highest value in column H based on the Load Group selection (column D)

For example.... I would like the cell that contains the value of "32" to be highlighted as the highest value for Load Group "D. Motors"

Thanks in advance.

[FlameRetired]

See the stripped down version and this formula in Format Manager.

Please Login or Register  to view this content.

[Arran [BMI]]

Thank you for the help with this one. I have applied the formula and it works great. I have however discovered that when applied to the entire range, any cells that do not have any data in them also follow the selected formatting?

So, based on the example that you have supplied. If you were to delete the values in D17 and H17, H17 also highlights.

Any thoughts?

[Arran [BMI]]

I have just done some more testing and found a couple of further issues. If I apply further conditional formatting to include the other load groups, (i.e. A. Lighting, B. Socket Outlets etc.) It will only highlight the highest value of any of the groups.

So based on the result that you have supplied, if D. Motors does not have the overall highest value (i.e. one of the values in A. Lighting is higher) no cell will be highlighted.

[FlameRetired]

Thank you for the help with this one. I have applied the formula and it works great. I have however discovered that when applied to the entire range, any cells that do not have any data in them also follow the selected formatting?

So, based on the example that you have supplied. If you were to delete the values in D17 and H17, H17 also highlights.

Any thoughts?

Try this CF formula

Please Login or Register  to view this content.

[FlameRetired]

I have just done some more testing and found a couple of further issues. If I apply further conditional formatting to include the other load groups, (i.e. A. Lighting, B. Socket Outlets etc.) It will only highlight the highest value of any of the groups.

So based on the result that you have supplied, if D. Motors does not have the overall highest value (i.e. one of the values in A. Lighting is higher) no cell will be highlighted.

Then with a drop down in F1 this CF formula.

Please Login or Register  to view this content.

[Arran [BMI]]

Excellent, that has worked perfectly. Thank you for your assistance.

[Arran [BMI]]

I have another quick question. Can the supplied formula, which works a treat be changed slightly to show other values such as the second highest?

[FlameRetired]

Yes.

Please Login or Register  to view this content.

The 2 ... k value ... can be replaced with reference to a cell just as Load Groups are in the drop down.

Just understand that if there are two MAX values the second LARGEst value is also the MAX.

Did you want something else instead?

[Arran [BMI]]

Once again, many thanks... Worked a treat....

[FlameRetired]

You're welcome. Thanks for the feedback and marking your thread Solved. It helps.