Count cells by color in a spreadsheet (cells coloured by conditional formatting)

Macca69

New Member
Joined
Aug 13, 2014
Messages
33
Hi there good people!
I'm trying to get excel to count the number of cells in a range (column or row) after the cells have been coloured by conditional formatting rule that stipulates "if the value in the cell is higher than a certain value, the shade the cell in a colour ..." so to speak.

I have tried using VBA and created a formula below and (i think!) saved it in the C Drive / Microsoft / Adins folder ...

Is the script I am using an old one and has the color index changed or do I have to define it ... or what is not working??

I am getting a "0" in the return, which kind of suggests the formual is working, just not recognising the cells or cell colour?

SCRIPT (as follows) and then added as a User Defined function
Code:
Function CountCcolor(range_data As Range, criteria AsRange) As Long
    Dim datax AsRange
    Dim xcolor AsLong
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    Ifdatax.Interior.ColorIndex = xcolor Then
        CountCcolor= CountCcolor + 1
    End If
Next datax
 
End Function
Hopefully this screen shot will work, but basically the following shows the range first, then the reference cell, then the screen shot shows the result ...


Sick Leave
21.75
0
0
21.75
39.75
14
37
Indicates sick leave hours in excess of the pro-rated 5 days (8 days allowed
1860 Total3.00 0
<colgroup><col width="64" style="width: 48pt;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5266;"> <tbody> </tbody>
in the budget tool (departmental average))
=CountCcolor(N7:N9,H238)

Sorry, the colur shading keeps copying ... but the return in this instance should have showed "2" as in two cells coloured "blue"

Any help greatly appreciated please!!
<colgroup><col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;"> <tbody> </tbody>
<colgroup><col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <tbody> </tbody>
 
Last edited by a moderator:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

If it is a simple one condition format, then =countif should suffice.

I can show you how to count colors or colorindexes if either the manually set fill color, as in your code, or the conditional format fill is that. It is pretty simple, one uses DisplayFormat. e.g.
Code:
debug.print Range("H2").displayformat.Interior.ColorIndex

When posting code, you can insert the code tags by typing them or clicking the # icon on the toolbar.
 
Upvote 0
Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

Thanks for the reply Kenneth
Unfortunately I'm not a programmer or anything like that and I'm not able to follow your response properly.

I'm using what I thought I was shown as a way of creating a function in the user defined functions, that can then be used in any other spreadsheets.

It is in conditional formatting as the data or conditions can change, and thus it will return different numbers of cells and differently shaded cells.

It was working once in 2014 when the CountCcolor script was set up in my old computer C Drive and under microsoft asins folder ... but the computer was replaced and I lost that file ... which meant it stopped working in the old file. I then created the new file in VBA and added that to the C Drive folder and into user define functions, then referenced that formula in this file ... but it is giving returns of zero when it should return numebrs as cells are shaded ... hence I am wondering if the scipt wording using color.index is now incorrect in this excel version

This is a work file and thus I can't attach thedata sadly and it is in excel 2013 ...

Any help from anyone greatly appreciated thanks!!
Troy
 
Upvote 0
Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

Just as a further bit of food for thought on the above VBA script being used as a "user defined" function, it appears to work in the same spreadsheet BUT for using cells that I immediately shade the cell colour myself manually ... see below
3
<colgroup><col width="8" style="width: 6pt; mso-width-source: userset; mso-width-alt: 292;"> <col width="64" style="width: 48pt;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5266;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="168" style="width: 126pt; mso-width-source: userset; mso-width-alt: 6144;"> <tbody> </tbody>
=CountCcolor(A238:G238,H238)

So, in the above, the "countCcolor" function returned the correct value of "3" when shading 3 of the six cells in that range the same as the reference colour (in this eg Cell H238)

So, the script / formula as a user defined function works OK and counts cells in Excel 2013 ... AS LONG AS they are not changed cell colour by a "conditional formatting" function ....

Can someone please help me overcome this obstacle now??

Many thanks in advance!!
Troy
 
Upvote 0
Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

Colors do not trigger an event to do an update. There are some ways to sort of make that happen or at least force an update. One is to replace the formula by code or use a selection event. For the latter, see: Simulating A Color Change Event in: Color Functions In Excel

I will whip up the manual and conditional format solution that I explained.
 
Last edited:
Upvote 0
Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

I prefer color property rather than colorindex. This routine lets you submit the color value or a cell with the displayformat color that you want.

The Sub's Debug.Print after a run puts the result in the Immediate Window. Select a cell with the interior color format that you want to get the number for and run it. In the VBE View menu, add the option to display that window if needed.
Code:
'conditional format rule for interior color red for A1:A10, =AND(B1>=0, ISBLANK(B1)=FALSE)
'B1=1, and A3 and A5 were manually set to interior color red. Result is a count of 3.
'C1 has the interior color of red, =cdcolor(A1:A10,C1), or, =cdcolor(A1:A10,255)

Function cdColor(cRange As Range, aColor) As Long
  Dim c&, r As Range, ac&

  If TypeName(aColor) = "Range" Then
    ac = aColor.DisplayFormat.Interior.Color
    Else
      ac = aColor
  End If

  For Each r In cRange
    If r.DisplayFormat.Interior.Color = ac Then _
      c = c + 1
  Next r
  
  cdColor = c
End Function

Sub GetColorOfActiveCell()
  Debug.Print ActiveCell.DisplayFormat.Interior.Color '255=red=vbRed
End Sub
 
Last edited:
Upvote 0
Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

Thanks again for your response Kenneth

I'm still not following you ... you write / think at too high a level for me I guess to follow.

Are you suggesting that I completely re-write a formula based function? Or are you suggesting that I add this to the previous one?

I'm not wanting anything to define a specific colour ... but rather recognise all colours, depending on what is chosen ... also by adding it into the Adins, it becomes available to be used in any other spreadsheet ...

Does the above that you have provided work with conditional formatting? The problem with my original script is that it works fine if you colour a cell yourself, but not if you tell conditional formatting to change the cell colour after a result is determined.

Interestingly enough, if you actually go into the range of cells subject to conditional formatting, select a cell that has changed colour, and then click on the " format cells" option and look at the cell shading colour .... it still shows as no colour, even though on the spreadsheet it appears blue or red etc .... clearly it is an issue with conditional formatting.

Further, I am sure I had this working in an older version of excel ...

Thanks for your time / thoughts again ... not sure what to do with what you have provided ...
 
Upvote 0
Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

Hi again Kenneth

Further to the above, I have copied your script into a blank excel file and gone into VB and added it in the module there. Saved the file as an xlam file in the Microsoft Adins folder and named it cdColor as a function Ad in.

I have then run the file with the same set up ... ie a series of values in cells A1:A10 and the conditional format to change the cell colour to red if above 20 ... reference cell for red is C1.

I have then selected the cdColor function as above and the result returned is #value (answer should be 3). Please see data below as screen dumps etc.
15
18
20
22
7
28
13
9
17
12
#VALUE!
<colgroup><col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>
=cdColor(A1:A10,C1) {this is the selected fiunctions from user defined function cdColor in the #value result}

SCRIPT:
Function cdColor(cRange As Range, aColor) As Long
Dim c&, r As Range, ac&
If TypeName(aColor) = "Range" Then
ac = aColor.DisplayFormat.Interior.Color
Else
ac = aColor
End If
For Each r In cRange
If r.DisplayFormat.Interior.Color = ac Then _
c = c + 1
Next r

cdColor = c
End Function
Sub GetColorOfActiveCell()
Debug.Print ActiveCell.DisplayFormat.Interior.Color '255=red=vbRed
End Sub

I cannot include a screen shot or snipped picture here it seems, but again with this routine and function, if I click on cell A3 (which has turned red) and select format cells and look at the fill colour ... it is showing as "blank" not "red" ....

OK, so have aI performed something incorrectly or is something not working correctly or?????

Many thanks yet again!! (it's a perplexing problem and I have performed a search in these threads and the issue appears to have been raised in the past ... by Sarah in March 23 2016 with some responses BUT it appears no solution .... also other options in past years as well ....

Over to the experts ...
 
Upvote 0
Re: How do I count cells by color in a spreadsheet (cells coloured by condidtional formatting)

Hi again

Thanks Kenneth for response again ... unfortunately our work systems won't allow me to access drop box ...

Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top