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

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

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

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

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