VBA code that counts colors of cells; looping (and printing count) after reaching specified color



I am working on a VBA code that will count the number of times a specified color of a cell appears in a range of cells; for example, count all cells in a range with the color yellow.

The following VBA code accomplishes this task:

Function CountCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

I have been trying to add a loop into this code that will loop each time another specified color (for example, purple) appears. Before looping I want the previous value displayed and then the count reset to 0.

Does anyone have any tips or solutions to this?

Thank you!




Check out Filters & Subtotal formulas...... link: https://www.youtube.com/watch?v=18tFxkF2dD4

it combines filtering and subtotaling. 


Answer the Question

You must create an account to use the forum. Create an Account or Login