How can I count same colours using vba?

agasi

New Member
Joined
Nov 22, 2016
Messages
48
In column A, there are several different colours (red, green, yellow or white etc). I need to count each colours. In column B, I want to see total number (eg. If there are three rows in red, each row shows 3,3,3, if there two rows in yellow, each row in B shows 2,2 etc))
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

agasi

New Member
Joined
Nov 22, 2016
Messages
48
I saw that one already but that is not what I am looking for. I need total numbers right next each row.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
If results incorrect , please show Example an expected results.
Code:
[COLOR="Navy"]Sub[/COLOR] MG02Dec04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("A1:A50")
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Interior.Color) [COLOR="Navy"]Then[/COLOR]
    .Add Dn.Interior.Color, Dn
[COLOR="Navy"]Else[/COLOR]
    [COLOR="Navy"]Set[/COLOR] .Item(Dn.Interior.Color) = Union(.Item(Dn.Interior.Color), Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    .Item(K).Offset(, 1).Value = Mid(Application _
    .Rept("," & .Item(K).Count, .Item(K).Count), 2)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

agasi

New Member
Joined
Nov 22, 2016
Messages
48

ADVERTISEMENT

Mick

Thanks a lot. Very close. But I don't want any number appears if it is white colour (Sorry I did not make myself clear in the original post). Colour code is to show they are duplications. It is like =COUNTIFS($A$5:$A$12,A5) in Excel.

Desricption
Count
Test in white
Test in white
Yellow
3
Red
2
Testx in white
Yellow
3
Red
2
Yellow
3

<tbody>
</tbody>
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG02Dec13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("A1:A50")
   [COLOR="Navy"]If[/COLOR] Not Dn.Interior.ColorIndex = xlNone [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Interior.Color) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Interior.Color, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Dn.Interior.Color) = Union(.Item(Dn.Interior.Color), Dn)
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    .Item(K).Offset(, 1).Value = .Item(K).Count
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

agasi

New Member
Joined
Nov 22, 2016
Messages
48
Mick

It works. That is what I just needed. Thank you so much. If possible, can you please explain the codes? I just have stepped through the code but I just don't have any idea as I am still very new to VBA.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,524
Messages
5,659,315
Members
418,496
Latest member
WHYCHumphrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top