How to Count and Sum Cells by Color in Excel Using VBA Macro

Are you tired of manually counting cells with specific colors in your Excel spreadsheet? Do you want to automate this process to save time and increase efficiency? If so, then a VBA macro can help. In this article, we will show you how to create a VBA macro that counts and sums cells by color in Excel.

Step-by-Step Guide

To get started, open your Excel workbook and press Alt + F11 to open the Visual Basic Editor (VBE). In the VBE, click on 'Insert' > 'Module' to insert a new module. Then, paste the following code:

`MsgBox 'Count=' & cntRes & vbCrLf & 'Sum= ' & sumRes & vbCrLf & _`

`SumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)`

`If indRefColor = Selection(indCurCell).Then`

`IndRefColor = cellsColorSample.Cells(1, 1).`

`If Not (cellsColorSample Is Nothing) Then`

`Select sample color:', 'Select a cell with sample color', _, Type:=8)`

This code uses the `WorksheetFunction.Sum` function to sum up all cells with the specified color. The `indRefColor` variable is used to store the reference color, and the `cellsColorSample` object is used to select the sample color.

How it Works

To use this macro, simply run it by pressing Alt + F8 to open the Macro dialog box, selecting the macro from the list, and clicking the 'Run' button. The macro will then prompt you to select a cell with the desired color.

Once you have selected the cell, the macro will count and sum all cells with that same color in your worksheet. The results will be displayed in a message box with the following format:

`Count= 'number' Sum= 'sum'`

Tips for Optimization

To make your VBA macros run faster, consider adding the following lines of code to your module:

`Application.ScreenUpdating = False`

`Application.Calculation = xlCalculationManual`

These lines turn off screen refresh and recalculating workbook formulas before running the macro. After the code is executed, everything is turned back on.

By following these steps and tips, you can create a VBA macro that counts and sums cells by color in Excel quickly and efficiently. This will save you time and increase your productivity in your daily work with Excel.

In this article, we have shown you how to create a VBA macro that counts and sums cells by color in Excel. We also provided tips for optimizing the performance of your VBA macros. With these steps and tips, you can automate repetitive tasks and increase your efficiency in working with Excel spreadsheets.

Excel macro download