So, here comes the code: Function Wbk Count Cells By Color(cell Ref Color As Range) Dim v Wbk Res Dim wsh Current As Worksheet Application. Calculation = xl Calculation Manual v Wbk Res = 0 For Each wsh Current In Worksheets wsh Current. Calculation = xl Calculation Automatic Wbk Count Cells By Color = v Wbk Res End Function Function Wbk Sum Cells By Color(cell Ref Color As Range) Dim v Wbk Res Dim wsh Current As Worksheet Application. Calculation = xl Calculation Manual v Wbk Res = 0 For Each wsh Current In Worksheets wsh Current. Calculation = xl Calculation Automatic Wbk Sum Cells By Color = v Wbk Res End Function, respectively.
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.
I will show you here how to use this code can take it to a completed application with all its bells and whistles.So, move on with the 5 quick steps below and you will know the number and sum of your color cells in a few minutes.The VB script below was written in response to Connor's comment (also by our Excel's guru Alex) and does exactly what Connor requested, namely counts and sums the cells of a certain color in all worksheets of the workbook.Besides that nearly all those VBA codes have a number of specificities and limitations because of which they may not work correctly with certain workbooks or data types. Color Then cnt Res = cnt Res 1 sum Res = Worksheet Function.All in all, you can try your luck and google for an ideal solution and if you happen to find one, please do come back and post your finding here! Color For ind Cur Cell = 1 To (cnt Cells - 1) If ind Ref Color = Selection(ind Cur Cell). Sum(Selection(ind Cur Cell), sum Res) End If Next Msg Box "Count=" & cnt Res & vb Cr Lf & "Sum= " & sum Res & vb Cr Lf & vb Cr Lf & _ "Color=" & Left("000000", 6 - Len(Hex(ind Ref Color))) & _ Hex(ind Ref Color) & vb Cr Lf, , "Count & Sum by Conditional Format color" End Sub functions ready for use and try them on your data. When we published this article, we hoped it would be popular because we used to get a lot of questions about how to count and sum cells by color in Excel.