In this tutorial lets see how we can SUM cells in Excel with similar colors with the help of VBA.
The dataset we are going to use:
Now go to VBE by pressing ALT + F11 and the insert a new Module.
Now create a new Function and give some space so that you can write the code.
Next declare 2 variables; Cell and Result, they will iterate and SUM respectively. After that start a For Each loop over LookupArray object. And at the end assign Result variable to SUMBYCOLOR as shown in the below image
Next we need to use an If statement to check if the color matches as well as if the values of the cell are same, because we don't want to SUM just by color, otherwise it will SUM different brands with same color. You can remove the code after AND if you want.
Now just go back to Excel and use the functions that you have created.
Complete code:
Comments