Excel VBA帮助 - 从一个单元格中获取一个数量，并通过添加多个单元格找出相同的数量，如果是，则更改多个单元格的颜色 [英] Excel VBA Help - Take one amount from one cell and find out the same amount by adding multiple cells, if yes change the colour of the multiple cells

问题描述

我是VBA的新人。我想做一个宏，如下面的步骤。

从一个单元格中取一个数量，并通过添加多个单元格找出相同的数量，如果是，则更改多个单元格的颜色。

示例：我的选择是整行A，单元格A1有20.00，A2有5.00，A3有1.00，A4有25.00，A5有14.00 ......宏必须为单元格A2，A3和A5标记蓝色，因为总价值等于单元格A1。
如果有任何帮助，这将是很好的。

提前感谢您的帮助!!

干杯，
Venkat

I am new person in VBA. I would like to do a macro like below steps.

Take one amount from one cell and find out the same amount by adding multiple cells, if yes change the colour of the multiple cells.

Example : my selection is entire A row, Cell A1 has 20.00, A2 has 5.00 , A3 has 1.00, A4 has 25.00, A5 has 14.00 ... the macro has to mark blue color for cells A2,A3 and A5, as the total value equals to cell A1.

It would be great, if any one help on this.

Thanks in advance for your help!!

Cheers,
Venkat

推荐答案

如果你想检查所有的组合，你不能在整行上这样做数字，permutaions的数量太大。如果你有A1的总和和A2-A4的一些数字，那么你需要检查A2，A3，A4，A2 + A3，A2 + A4，A3 + A4和A2 + A3 + A4这七种组合。如果你的范围是A2到A9，这将变成40,321种组合，当你达到A2 - A21的范围时，这将成为2,432,902,008,176,640,001组合检查。您可能需要通过对列进行排序并找到数据已经大于总数的行来限制您的检查，然后检查组合到那一点。

Hi, you cannot do this for an entire row, if you want to check all combinations of numbers, the number of permutaions is too great. If you have you total in A1 and some numbers in A2-A4 then you would need to check A2, A3, A4, A2+A3, A2+A4, A3+A4 and A2+A3+A4 which is seven combinations. If your range is A2 to A9 this becomes 40,321 combinations, by the time you reach the range A2 - A21 this becomes 2,432,902,008,176,640,001 combiantions to check. You would need to limit your checking perhaps by sorting the column and finding the row where the data is already greater than your total and then check the combinations up to that point.

这篇关于Excel VBA帮助 - 从一个单元格中获取一个数量，并通过添加多个单元格找出相同的数量，如果是，则更改多个单元格的颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！