![kutools in excel 2013 kutools in excel 2013](https://i.ytimg.com/vi/R7j0HzOUI38/maxresdefault.jpg)
The same applies to the other macros you will find further in this article. So, after coloring cells manually, simply place the cursor to any cell and press F2 and Enter, the sum and count will get updated. The point is that all such functions are called with a change of a worksheet's data only and Excel does not perceive changing the font color or cell color as a data change. In fact, it is the normal behavior of all Excel macros, VBA scripts and User-Defined Functions.
![kutools in excel 2013 kutools in excel 2013](https://img.informer.com/pb/kutools-for-excel-v23-main-window-display.png)
Kutools in excel 2013 code#
Please don't be angry with us, this is not a bug of the code : ) Note: If after applying the above mentioned VBA code you would need to color a few more cells manually, the sum and count of the colored cells won't get recalculated automatically to reflect the changes. Now that all "behind the scenes" work is done for you by the just added user-defined function, choose the cell where you want to output the results and enter the CountCellsB圜olor function into it:.
Kutools in excel 2013 how to#
If you are not very comfortable with VBA, you can find the detailed step-by-step instructions and a handful of useful tips in this tutorial: How to insert and run VBA code in Excel.
![kutools in excel 2013 kutools in excel 2013](https://i.ytimg.com/vi/E_kHYSO9R5o/maxresdefault.jpg)
If indRefColor = Thenįunction SumCellsByFontColor(rData As Range, cellRefColor As Range) IndRefColor = cellRefColor.Cells(1, 1).Font.Color SumRes = WorksheetFunction.Sum(cellCurrent, sumRes)įunction CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long If indRefColor = Thenįunction SumCellsB圜olor(rData As Range, cellRefColor As Range) IndRefColor = cellRefColor.Cells(1, 1).Interior.Color ReDim arResults(1 To, 1 To )įor indColumn = 1 To ĪrResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Colorįunction GetCellFontColor(xlRange As Range)ĪrResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Colorįunction CountCellsB圜olor(rData As Range, cellRefColor As Range) As Long
![kutools in excel 2013 kutools in excel 2013](https://progsoft.net/images/kutools-for-excel-760deaf95e5cd7a854e499dbfa95d0dbe087eaa5.png)
If you know very little about this technology or have never heard this term before, don't be afraid, you will not have to write the code yourself. But regrettably, there is no formula that would let us sum by color or count by color in a usual Excel worksheet.Īpart from using third-party add-ins, there is only one possible solution - utilize User Defined Functions. the sum of all red cells.Īs all of us know, Microsoft Excel provides a variety of formulas for different purposes, and it would be logical to assume that there are some to count cells by color. If your cells' values are numbers, you may also want to automatically calculate the sum of cells shaded with the same color, e.g. If you actively use diverse fill and font colors in your Excel worksheets to differentiate between various types of cells or values, you may want to know how many cells are highlighted in a certain color. You will also learn how to filter cells by several colors in Excel 2010, Excel 2013, Excel 2016, and Excel 2019. These solutions work both for cells colored manually and with conditional formatting. I this article you will learn how to count cells by color in Excel and get the sum of colored cells.