color data count

ADRIANA P
ADRIANA P used Ask the Experts™
on
In this workbook I'd like

an

count  for every color by column

as shown

d1.PNG
data can grown  and shrink as needed

d1.PNGcount_by_color_to.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager
Commented:
I have a UDF that counts,sums or averages by coloured cells.

You can copy the code from this example and use in your own workbooks
ColourFunction.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
an Great Tool

Great  Expert

 Thanks !!
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try something like this...

Sub CountByColors()
Dim ws As Worksheet
Dim lr As Long, lc As Long, r As Long, c As Long
Dim clrB As Long, clrP As Long, clrG As Long
Dim cntB As Long, cntP As Long, cntG As Long, cntAM As Long, cntPM As Long
Application.ScreenUpdating = False
Set ws = Sheets("DTA")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If lr < 8 Then Exit Sub
lc = ws.Range("A8").End(xlToRight).Column
If lc < 3 Then Exit Sub

ws.Range("B2:B3").ClearContents
ws.Range(ws.Cells(4, 3), ws.Cells(6, lc)).ClearContents

clrB = ws.Range("A4").Interior.Color
clrP = ws.Range("A5").Interior.Color
clrG = ws.Range("A6").Interior.Color
For r = 8 To lr
    If ws.Range("B" & r).Value = "AM" Then
        cntAM = cntAM + 1
    ElseIf ws.Range("B" & r).Value = "PM" Then
        cntPM = cntPM + 1
    End If
Next r
ws.Range("B2").Value = cntPM
ws.Range("B3").Value = cntAM

For c = 3 To lc
    For r = 8 To lr
        If ws.Cells(r, c).Interior.Color = clrB Then
            cntB = cntB + 1
        ElseIf ws.Cells(r, c).Interior.Color = clrP Then
            cntP = cntP + 1
        ElseIf ws.Cells(r, c).Interior.Color = clrG Then
            cntG = cntG + 1
        End If
    Next r
    ws.Cells(4, c) = cntB
    ws.Cells(5, c) = cntP
    ws.Cells(6, c) = cntG
    cntB = 0
    cntP = 0
    cntG = 0
Next c

Application.ScreenUpdating = True
End Sub

Open in new window


In the attached, click the button called "Count By Colors" to run the code.
count_by_color_to.xlsm
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I think this is what you asked for. Click the 'Count Hits' button on Sheet1.
29120916.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
in  any ways this   is an  first class work

Thanks

Roy Cox ,  Subodh Tiwari (Neeraj) , Martin Liss
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!
Roy CoxGroup Finance Manager

Commented:
Sorry, I didn't have time last night. I've added the Function for you plus the formulas to count colours in each column.

The advantage of a Function is that it works like an inbuilt Function and will update automatically when a colour changes.
count_by_color_to.xlsm

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial