Conditional formatting to balance two values

[Richarizard]

I have two values (potentially more later) that are amounts spent by different parties that I want to balance.

I want to use a 3-Color scale, where a party that has contributed more will be more blue, and less will be more yellow. The closer to being balanced would gradient to the cells being green. I think I need to use the percentage of the contribution from each party, but I'm not entirely sure on how to set that up.

Any help would be greatly appreciated!

 

[Micron]

I want to use a 3-Color scale,

You want a gradient result or 3 distinct colours? Methinks that for the former, you will definitely need code that alters the percentage of blue vs yellow based on the inputs. That means it's really a 2 colour scale (blue-yellow) assuming you're going to leave red alone. Even easier if you can just alter one colour and range from 0 yellow (or 0 blue) to 255.

You don't say how many parties are involved so I think you would have to know that at the time of computation because the max of any colour value will be 255. If my contribution is 10% (25.5), the other 90% is spread over a variable population? You'd probably also have to round because I don't think you can assign 25.5 of yellow to me, and you can't go over 255, which would be possible with rounding.

EDIT - forgot to mention that CF can be done solely in code, or you can use CF dialogs to call a vba function, IIRC.

 

[Richarizard]

I was planning on using a gradient, and I was only going to have two parties to begin with, and then potentially expand from there.

I was hoping that I could do it with just CF formulas, but I couldn't figure out how.

I ended up calculating the percentage of each contribution in the cell below the totals, then applied a percentage CF to those, and used code to mirror the cell color onto the cell with the original totals. It feels like there should be a more elegant way to do it, but I got it done!