SUMIF on Cell Color without Macro?



I just saw your new tutorial on using the SUMIFS function to sum based on a cells background color and I wanted to know if it was possible to achieve a similar result without the use of a macro.

Here is the link to the tutorial:

(sorry it won't let me put a clickable link yet)

Any insight is much appreciated, thanks!



Selected Answer

No. That would not be possible. Here is the reason.

A cell can have only one color, just as it can have only one value. However, just as it is possible to display a cell's value in different formats so it is also possible to display a cell in a color which is not its own. This is the principle on which conditional formatting works: a cell has no color but it is displayed red.

In this scenario, if a formula would look at the cell and determine its color it would find that the cell has no color even though it is clearly and obviously red. The same, by the way, would hold true for VBA code examining the cell.

Now, imagine a formula which changes the cell color. Would it change the real cell color or would it just display the cell in another color? Excel's principle is that no formula can change any cell's own properties. To do so is the prerogative of the user, exclusively, or of VBA doing the user's bidding.

Therefore the answer to your question is that if you wish to change the apparent color of a cell use conditional formatting. On the other hand, if you want to change a cell's real color use VBA.



Ok I think I get it now. Thanks for the quick answer!
chriswall (rep: 6) Feb 21, '18 at 2:34 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login