Greater than formulas with conditional formatting

[Jamie]

Ahhh!!! About to pull my hair out! Hopefully someone out there can
help save my hair and my sanity by giving me some excel help! I
have roughly 17 cells that all contain sums calculated from other
cells. I would like to use conditional formatting (unless there is a
better suggestion) to highlight the highest sum. I've tried writting
a formula within the conditional formatting that would do this but
can't figure out how to write the formula that states if cell A3 is
greater than A4, A5, A6, and A7. I've tried it several different ways
but I keep getting an error message that says that I can't use
"unions, intersections, or array constants for Conditional Formatting
criteria." Any suggestions on how to write the formula? I also
ideally would like to rank the top 3 sums and highlight them different
colors but didn't see how that would be possible since I couldn't even
get the top one figured out! I can't simply rank the cells because
the sums are dynamic and will change depending on other inputs.

I appreciate any help! Thanks!
Jamie

 

[Debra Dalgleish]

Select the cells that you want to format (cells A3:A20 in this example)
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the text box, type a formula that refers to the active cell, e.g:
=A3=LARGE($A$3:$A$20,1)
Click the Format button, and select the formatting for the highest sum
Click OK, then click Add

Under Condition 2, from the first dropdown, choose Formula Is
In the text box, type:
=A3=LARGE($A$3:$A$20,2)
Click the Format button, and format the second highest sum
Click OK, then click Add

Under Condition 3, from the first dropdown, choose Formula Is
In the text box, type:
=A3=LARGE($A$3:$A$20,3)
Click the Format button, and format the third highest sum
Click OK, then click OK