|Doing this with Conditional Formatting and formulae is going to be extremely cumbersome. The conditional formatting of a cell is determined by an specific event (a condition) that can be evaluated to either TRUE or FALSE. Since you don't know which cells will be involved in the SUM, you have to determine that first and then have CF "find" those cells.|
In other words, how would you tell CF to format A1 & A3 only if A1 and A3 are included in the group of cells in the SUM function?
Let's look at the formula that would be needed to actually find which values would be included in the SUM. I'm not going to worry about multiplying C * B, I'm just going to use straight numbers so you can see the issue. In addition, I'm only going to use 3 numbers...I don't know how many you'll need to reach that $5000 mark.
Here's my data:
To find the highest values that sum to <=5000, I could use this to formula to display the actual values in a cell.
=IF(SUM(LARGE(A1:A3,1),LARGE(A1:A3,2))>5000,LARGE(A1:A3,1),IF(SUM(LARGE(A1:A3,1),LARGE(A1:A3,2),LARGE(A1:A3,3))>5000,LARGE(A1:A3,1)&" "&LARGE(A1:A3,2),A1&" "&A2&" "&A3))
which in this case would return:
Basically what it does is add up the 2 highest values and if the sum is greater than 5000, it will display just the highest value. If the sum of the 3 highest values is greater than 5000, it will display the 2 highest, and finally if the sum of all 3 values is less than 5000, it will display them all.
Of course the issue gets even more complicated if you have a large number of values that will be included in the sum, since you can only have 7 Nested If's in a single if statement.
OK, now that I've got the values, I need to do something within CF to find those values in the actual list and CF them.
There are multiple ways to attack this, but none of them are straighforward.
This seems more like something that should be handled via VBA.