CODEWITHSUNDEEP
vbaexcel
Joey Zhou
Joey Zhou

Reputation: 57

How to count cells in a specific color in all worksheets in excel

is there a way I can count the number of colored cells in the whole workbook? For example I'm trying to get a count of all the green cells in my workbook, but since the work book has so many tabs that I have to do sort in each tab then add them up at the end. It's really a waste of time since I have many workbooks with more than 20 tabs each. Please help! :D Thanks!

Upvotes: 3

Views: 1266

Answers (2)

Vasily
Vasily

Reputation: 5782

this code does the count of cells for each color in all worksheets, then creates new workbook and fills with colors and counts:

Sub test()
    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
    dic.comparemode = vbTextCompare
    Dim cl As Range, i&, ws As Worksheet, k      
    For Each ws In ThisWorkbook.Worksheets
        For Each cl In ws.UsedRange
            If Not dic.exists(cl.Interior.Color) Then
                dic.Add cl.Interior.Color, 0
            Else
                dic(cl.Interior.Color) = CLng(dic(cl.Interior.Color)) + 1
            End If
        Next cl
    Next ws
    Workbooks.Add: i = 1
    For Each k In dic
        Cells(i, "A").Interior.Color = k
        Cells(i, "A").Value2 = "Interior.Color = " & k
        Cells(i, "B").Value2 = dic(k)
        i = i + 1
    Next k
End Sub

output:

enter image description here

Upvotes: 2

Warren Sergent
Warren Sergent

Reputation: 2597

There is an article from Microsoft, here: https://support.microsoft.com/en-us/kb/2815384

It details step by step instructions on doing very similar to what you are trying to do.

The VBA function required is as follows:

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

Upvotes: 2

Related Questions