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.You will find the perfect code (written by our Excel guru) here and all that you will have to do is copy / paste it into your workbook. What we want now is automatically count cells by color, i.e.But regrettably, there is no formula that would let us sum by color or count by color in a usual Excel worksheet.Apart from using third-party add-ins, there is only one possible solution - utilize User Defined Functions.Sub Sum Count By Conditional Format() Dim ind Ref Color As Long Dim cell Current As Range Dim cnt Res As Long Dim sum Res Dim cnt Cells As Long Dim ind Cur Cell As Long cnt Res = 0 sum Res = 0 cnt Cells = Selection. What you see in comments on this page is just a tiny portion of the enormous feedback we have received.So, our team decided to take a step further and create an Excel add-in that would count and sum cells by the color you specify or by all colors in the selected range.It is saving the workbook before mailing it with a date/time stamp. Excel will automatic update the reference number to Outlook when you open your workbook in a higher version of Excel/Outlook but not update it when you open it in a lower version of Excel/Outlook.
If you have applied conditional formatting to color cells based on their values and now you want to count cells by color or sum the values in colored cells, I have bad news - there is no universal user-defined function that would sum by color or count color cells and output the resulting numbers directly in the specified cells.
Important read this : The code on this page is only working when you use Outlook as your mail program. This are the main formats in Excel 2007-2016 : 51 = xl Open XMLWorkbook (without macro's in 2007-2016, xlsx)52 = xl Open XMLWorkbook Macro Enabled (with or without macro's in 2007-2016, xlsm)50 = xl Excel12 (Excel Binary Workbook in 2007-2016 with or without macro’s, xlsb) 56 = xl Excel8 (97-2003 format in Excel 2007-2016, xls) If you always want to save in a certain format you can replace this part of the macro Select Case Sourcewb.
Copy the code in a Standard module of your workbook, if you just started with VBA see this page. File Format Case 51: File Ext Str = ".xlsx": File Format Num = 51 Case 52: If .
Activate v Wbk Res = v Wbk Res Count Cells By Color(wsh Current. Activate v Wbk Res = v Wbk Res Sum Cells By Color(wsh Current. Simply enter either formula in any empty cell on any sheet without defining a range, specify the address of any cell of the needed color in brackets, e.g.
=Wbk Sum Cells By Color(A1), and the formula will display the sum of all the cells shaded with the same color in your workbook.