• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count color cell both (Manual and CF) with VBA

Jagdev Singh

Active Member
Hi Experts,

Please let me know if this is possible or not. I have a sheet which contains red cell with both Manual and Conditional Formatting red colored cell. I want to count the number of cells with both options available in the sheet.

In the attached sample column A is the manually added red color cell. The column B is the conditional formatting driven red colored cells. The output of this is total number of cells available in the sheet i.e. 3.

Kindly let me know if this is feasible or not.

Regards,

JD
 

Attachments

  • Sample.xlsx
    8.4 KB · Views: 7
If using manual format, use one of UDFs that's out there.
https://support.microsoft.com/en-us...f-cells-with-specific-cell-color-by-using-vba

Unfortunately, there isn't direct way to count CF color using UDF.

Alternatives are.
1. Write enter operation in VBA, then have the code put back the value to desired location. I believe this is only possible for Excel 2010 or later.

2. Since it's CF, there must be underlying logic that determines color. So, count the values that meet the condition in range using COUNTIF, etc.
 
Chihiro is correct; you can write a function to count colours including conditionally formatted colours (using .DisplayFormat) and it will work when called by another sub, it just won't work when you try to call it from a worksheet cell. Most Excel MVPs consider this to be a bug. .DisplayFormat is only available from Excel 2010 onwards.
In the attached, I've written a sub to count colours whether from CF or manually added, which you can call manually, or by clicking the button on the sheet, or you could assign it a hot-key combination.

It works like this:
1. Select the area(s) you want to count the colours in. If you're selecting several non-contiguous areas make sure they don't overlap else you might get colours counted more than once.
2. Make the last selection the single cell whose colour you want to count. Hold the Ctrl key down while selecting the last cell (and to select non-contiguous areas).
3. Run the macro.
The count will appear in the cell immediately to the right of the single coloured cell you selected at the end.
The code:
Code:
Sub ColourCount()
Set TheRange = Selection
With TheRange.Areas(TheRange.Areas.Count)
  lCol = .DisplayFormat.Interior.Color
  Set OutPutCell = .Offset(, 1) 'this is what decides which cell the count appears in.
End With
For i = 1 To TheRange.Areas.Count - 1
  For Each rCell In TheRange.Areas(i).Cells
    If rCell.DisplayFormat.Interior.Color = lCol Then vResult = 1 + vResult
  Next rCell
Next i
OutPutCell.Value = vResult
'MsgBox vResult
End Sub
 

Attachments

  • Chandoo35434Sample.xlsm
    18 KB · Views: 19
Last edited:
Hi p45cal and Chihiro,

Thanks for the above information and code. This is really helpful. Appreciated your input on this!

Regards,
JD
 
Chihiro is correct; you can write a function to count colours including conditionally formatted colours (using .DisplayFormat) and it will work when called by another sub, it just won't work when you try to call it from a worksheet cell. Most Excel MVPs consider this to be a bug. .DisplayFormat is only available from Excel 2010 onwards.
In the attached, I've written a sub to count colours whether from CF or manually added, which you can call manually, or by clicking the button on the sheet, or you could assign it a hot-key combination.

It works like this:
1. Select the area(s) you want to count the colours in. If you're selecting several non-contiguous areas make sure they don't overlap else you might get colours counted more than once.
2. Make the last selection the single cell whose colour you want to count. Hold the Ctrl key down while selecting the last cell (and to select non-contiguous areas).
3. Run the macro.
The count will appear in the cell immediately to the right of the single coloured cell you selected at the end.
The code:
Code:
Sub ColourCount()
Set TheRange = Selection
With TheRange.Areas(TheRange.Areas.Count)
  lCol = .DisplayFormat.Interior.Color
  Set OutPutCell = .Offset(, 1) 'this is what decides which cell the count appears in.
End With
For i = 1 To TheRange.Areas.Count - 1
  For Each rCell In TheRange.Areas(i).Cells
    If rCell.DisplayFormat.Interior.Color = lCol Then vResult = 1 + vResult
  Next rCell
Next i
OutPutCell.Value = vResult
'MsgBox vResult
End Sub

Dear p45cal

Fantastic. I've tried many solutions out there but nothing works

Would you please modify the Solution so that we can use it as Formula = CountColour (Range, Colour Code) with auto-update upon new CF colour detection

Here's another solution but not working for me despite following its steps


KIndly see if you can understand & make a working solution for CF :)

Thanks
 

Attachments

  • CountColorCells.xlsm
    23.5 KB · Views: 4
Nope. The same 'bug' exists 4 years later still.
What you can do is follow:
Since it's CF, there must be underlying logic that determines color. So, count the values that meet the condition in range using COUNTIF, etc.
The formula might be:
=SUM(--(COUNTIF(O:T,B2:K4)>0))
 
Nope. The same 'bug' exists 4 years later still.
What you can do is follow:

The formula might be:
=SUM(--(COUNTIF(O:T,B2:K4)>0))

Dear @p45cal,

Thanks for your kind reply, I've used your suggested formula in the excel above. it Retuned me ZERO :oops:
 
76547
Perhaps your version of Excel needs the formula to be array-entered for which you'll have to unmerge those cells first (although you can merge them again afterwards) Array-entering is pressing Ctrl+Shift+Enter to commit the formula to the sheet rather than the usual plain Enter.
Bear in mind this formula pays no attention to cell colour.
 
View attachment 76547
Perhaps your version of Excel needs the formula to be array-entered for which you'll have to unmerge those cells first (although you can merge them again afterwards) Array-entering is pressing Ctrl+Shift+Enter to commit the formula to the sheet rather than the usual plain Enter.
Bear in mind this formula pays no attention to cell colour.

Dear @p45cal,

Thanks for your kind reply, it's working Yaay!

Please check your Inbox, I got issues with actual data using your formula. i need your help to adjust its criteria

Thanks :)
 
Last edited:
Depending on your version of Excel try:
=SUM(--(Table9[[Vendor 1 Price]:[Vendor 4 Price]]=Table9[Lowest Price (Rs)]))
or:
=SUMPRODUCT(--(Table9[[Vendor 1 Price]:[Vendor 4 Price]]=Table9[Lowest Price (Rs)]))
The above assumes the intervening Qty columns will never be the same as the Lowest Price column.

If that assumption is wrong then you can:
=SUM((Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))
or:
=SUMPRODUCT((Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))

Individual columns:
C1:
=SUM(1*(Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)]))

E1:
=SUM(1*(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)]))
etc.

[The file you attached to your message in the Private Conversation you opened with me didn't seem to have any particularly sensitive data in it. I leave it to you to attach it in this thread too so that others can learn from your problem.]
 
Last edited:
Re: "Please check your Inbox"
I was under the impression that this is an open forum, not a private email forum.

Dear jolivanes

Indeed it is Buddy. I'm contacting him to help me with actual data where scenario is slightly different. Don't worry we will share the out come :)
 
Depending on your version of Excel try:
=SUM(--(Table9[[Vendor 1 Price]:[Vendor 4 Price]]=Table9[Lowest Price (Rs)]))
or:
=SUMPRODUCT(--(Table9[[Vendor 1 Price]:[Vendor 4 Price]]=Table9[Lowest Price (Rs)]))
The above assumes the intervening Qty columns will never be the same as the Lowest Price column.

If that assumption is wrong then you can:
=SUM((Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))
or:
=SUMPRODUCT((Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))

Wow, I just replied in the Thread & got your Msg :) Let me give it a try.

I've got Excel 2010 Version in the office while at home got 2019 Version
 
Depending on your version of Excel try:
=SUM(--(Table9[[Vendor 1 Price]:[Vendor 4 Price]]=Table9[Lowest Price (Rs)]))
or:
=SUMPRODUCT(--(Table9[[Vendor 1 Price]:[Vendor 4 Price]]=Table9[Lowest Price (Rs)]))
The above assumes the intervening Qty columns will never be the same as the Lowest Price column.

If that assumption is wrong then you can:
=SUM((Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))
or:
=SUMPRODUCT((Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))

Individual columns:
C1:
=SUM(1*(Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)]))

E1:
=SUM(1*(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)]))
etc.

[The file you attached to your message in the Private Conversation you opened with me didn't seem to have any particularly sensitive data in it. I leave it to you to attach it in this thread too so that others can learn from your problem.]

Hi

I've tried all of them & they Returned me 78 Number. Looks like Cell needs to be referenced e.g C1 instead of a Table Column Name

Are they formulas working for you in the Provided excel sheet

Thanks
 
Depending on your version of Excel try:
=SUM(--(Table9[[Vendor 1 Price]:[Vendor 4 Price]]=Table9[Lowest Price (Rs)]))
or:
=SUMPRODUCT(--(Table9[[Vendor 1 Price]:[Vendor 4 Price]]=Table9[Lowest Price (Rs)]))
The above assumes the intervening Qty columns will never be the same as the Lowest Price column.

If that assumption is wrong then you can:
=SUM((Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))
or:
=SUMPRODUCT((Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)])+(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))

Individual columns:
C1:
=SUM(1*(Table9[Vendor 1 Price]=Table9[Lowest Price (Rs)]))

E1:
=SUM(1*(Table9[Vendor 2 Price]=Table9[Lowest Price (Rs)]))
etc.

[The file you attached to your message in the Private Conversation you opened with me didn't seem to have any particularly sensitive data in it. I leave it to you to attach it in this thread too so that others can learn from your problem.]

You are Genius Buddy :)

G1: >> Woking
=SUM(1*(Table9[Vendor 3 Price]=Table9[Lowest Price (Rs)]))

I1: >> Displays 16 While Sorted by Colour to Test Counted = 13
=SUM(1*(Table9[Vendor 4 Price]=Table9[Lowest Price (Rs)]))

Any idea why the similar formula has got issues with Vendor 4 Counting Please

All other 3 Vendors Green Count is Perfect Except the 4th One :(

Thanks
 
Last edited:
Look at your conditional formatting; it doesn't cover the entire table.
Ops it was my own mistake that I've kept 1 vendor filter by Colour while checking others by green shorting colour

Your Solution Works Perfect :cool:
 
Back
Top