Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

SUMIF on Cell Color without Macro?

0

Hi,

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:

https://www.teachexcel.com/excel-tutorial/2036/sumif-s-based-on-cell-color?nav=home_page_new_tutorials

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

Any insight is much appreciated, thanks!

Answer
Discuss

Answers

0
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.

Discuss

Discussion

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