Log In
Register
Home
February 20, 2020
Offline
I am looking for a code that will paste automatically =COUNTIFCOLOUR formula to Range("C1:AZ1").
First add the row on top across all sheets then paste the formula to Range("C1:AZ1") where formula will count colored cells from C1:C5000, D1:D5000, E1:E5000 and so on.
Here are two RGB which formula will count RGB(244, 204, 204) & RGB(183, 225, 205)
Function COUNTIFCOLOUR(Colour As Range, rng As Range) As Long Dim NoCells As Long Dim CellColour As Long Dim rngCell As Range CellColour = Colour.Interior.Color For Each rngCell In rng If rngCell.Interior.Color = CellColour Then NoCells = NoCells + 1 End If Next COUNTIFCOLOUR = NoCells End Function
October 5, 2010
Offline
Hi HSAR,
Please supply workbooks when you are referring to data and it will be useful for those helping you. Otherwise we have to type everything in by hand.
I'm not clear on what you are asking. You want to count colored cells?
You can use this VBA Count, Sum and Average Colored Cells
or this non-VBA approach Count Colored Cells with SUBTOTAL
Regards
Phil
February 20, 2020
Offline
Can you please edit this code right now the code is counting the single column and gives accurate result when i add two or more columns then it gives same value for each column.
Public Sub CountColorCells()
'Variable declaration
Dim rng As Range
Dim lColorCounter As Long
Dim rngCell As Range
'Set the range
Set rng = Sheet2.Range("J2:J1000, K2:K1000")
'loop throught each cell in the range
For Each rngCell In rng
'Checking Yellor color
If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(183, 225, 205) Then
lColorCounter = lColorCounter + 1
End If
Next
'Display the value in cell
Sheet2.Range("J1,K1") = lColorCounter
End Sub
October 5, 2010
Offline
Hi HSAR,
You haven't supplied a workbook.
That VBA code isn't valid so it can't be giving an accurate result.
Did you try the code I linked to?
You haven't answered my question.
Regards
Phil
February 20, 2020
Offline
Dear, Philip Treacy
I have also been using same code to count colored based cell as you provided but right now i am looking for above 1 that i want you to modify. here is workbook
October 5, 2010
Offline
Hi Ali,
Please see the attached workbook. It uses the COUNTIFCOLOUR function and takes 2 arguments. The 1st is the cell that has the colour you want to count, the 2nd is range containing cells that may have the colour from the first argument.
On Row 1 of Sheet 2 there are 3 examples of how the function works e.g. in H1
=COUNTIFCOLOUR(G1,$A$3:$AC$42)
counts the 8 dark red cells in the range $A$3:$AC$42.
Regards
Phil
1 Guest(s)
