Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi loungers......I suspect that this is a rather difficult topic, but I am looking for either VBA or a 'regular' COUNT or COUNTIF function (??) that I can use to count the number of cells, based on color..........eg: if I have 23 red cells (red as the interior color), then I suspect that I need something that counts the interior.colorindex = 3.............I want to be able to color different cells manually, and count how many of each color I have......I have checked this lounge and can't find very much so far. I have also checked Chip Pearson's site but didn't find anything that I could understand well enough to be able to use......any suggestions? Thanks.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Counting cells of a given color is not at all as easy as you might think. Even with my fairly significant experience in this area, I had to do a tad of experimenting to work out how to get the count. To make matters worse, the documentation covers colors of objects in worksheets very poorly, IMO.

    Unfortunately, you will need to do a bit of experimenting, but I've given you a huge leg up with the following information and the attached example workbook.

    Function lngCountCellsOfAColor, shown in Listing 1, is the color counter. Immediately following it, Listing 2 shows the enumeration used to define the valid values of its third argument, penmWhichColor. In the context of a worksheet, the valid values are as follows.

    FontColor = 1
    BackgroundColor = 2

    Assuming that you want to call this function from a worksheet cell, the next hurdle is how to specify the range. To date, the only way I've been able to successfully pass a range to a function is by passing the sheet name and the range name in separate arguments. I'm sure there are more elegant ways to do it, using the Names collection, but, to date, I haven't been sufficiently motivated to delve into that. Therefore, the first and second arguments, pstrSheetName and pstrRanngeName, respectively, are strings.

    Finally, we come to the most challenging argument of all, pvarColor, which is defined as a Variant, and sparsely documented beyond that. To date, I've found that the simplest way to get a usable ColorIndex is to get the ColorIndex of a cell to which the desired color has been applied. To that end, I give you GetCellColor, shown in Listing 4, which is a primitiive, but workable subroutine, intended to be run from the Immediate window. The idea is that you hard code the name of the sheet, the coordinates of a cell of the desired color, and the enmerated constant to indicate whether you want the font color or the interior color. The value returned in the message box is the color index that you feed to the cell counter function.

    Listing 3 is a demonstration macro, imaginatively named main, which demonstrates lngCountCellsOfAColor by counting the cells in the heading of the KEYS worksheet in the attached demonstration workbook, which also contains a compiled, but unsigned, copy of all of the code shown here.

    In case you are wondering why the KEYS worksheet is empty, except for the headings and formatting, it is a gutted copy of a production worksheet that contains such things as my login password for this board.

    Code:
    Public Function lngCountCellsOfAColor( _
        	pstrSheetName As String, _
        	pstrRanngeName As String, _
        	penmWhichColor As enmColorProperties, _
        	pvarColor As Variant) _
            	As Long
    
    '   Function Name:  	lngCountCellsOfAColor
    '   Synopsis:       	Count cells whose interior or font color matches a given
    '                   	color index value.
    '
    '   In:             	pstrSheetName   = Worksheet Name
    '                   	pstrRanngeName  = Name of Range, which must be a Named,
    '                                     	or predefined, Range
    '                   	penmWhichColor  = A enmColorProperties value, as follows:
    '                                       	FontColor   	= 1 = Font (text)
    '                                                             	color
    '                                       	BackgroundColor = 2 = Background
    '                                                             	(interior)
    '                                                             	color
    '                   	pvarColor   	= ColorIndex value, which is defined as
    '                                     	a Variant.
    '
    '   Out:            	Count of cells having the specified color.
    '
    	If pstrSheetName <> vbNullString And pstrRanngeName <> vbNullString And penmWhichColor <> ValueNotSet Then
        	Dim rng As Range
        	Set rng = ActiveWorkbook.Worksheets(pstrSheetName).Range(pstrRanngeName)
    
        	Dim intTotCols As Integer: intTotCols = rng.Columns.Count
        	Dim lngTotRows As Long: lngTotRows = rng.Rows.Count
    
        	Dim intCurrCol As Integer
        	Dim lngCurrRow As Long
    
        	Dim rngThisCell As Range
    
        	Dim lngWorkingCount As Long
    
        	For lngCurrRow = 1 To lngTotRows
            	For intCurrCol = 1 To intTotCols
                	Select Case penmWhichColor
                    	Case FontColor
                        	If rng.Cells(lngCurrRow, intCurrCol).Font.ColorIndex = pvarColor Then
                            	lngWorkingCount = lngWorkingCount + 1
                        	End If
                    	Case BackgroundColor
                        	If rng.Cells(lngCurrRow, intCurrCol).Interior.ColorIndex = pvarColor Then
                            	lngWorkingCount = lngWorkingCount + 1
                        	End If
                	End Select
            	Next intCurrCol
        	Next lngCurrRow
    
        	lngCountCellsOfAColor = lngWorkingCount
    	Else
        	lngCountCellsOfAColor = 0
    	End If
    
    End Function
    Listing 1 is my color counter, lngCountCellsOfAColor. As written, it can count cells that are set in a given foreground (font) or background (interior) color.

    Code:
    	Public Enum enmColorProperties
        	ValueNotSet
        	FontColor
        	BackgroundColor
    	End Enum
    Listing 2 is the enumeration used to indicate, via the penmWhichColor argument, which part of the cell has the ColorIndex value specified by the fourth argument, pvarColor.

    Code:
    Sub main()
    
    	Const HEADING_INTERIOR_COLORINDEX As Integer = 55
    	Const KEYS_SHEET_NAME As String = "KEYS"
    	Const KEYS_ALL_RANGE_NAME As String = "rngEverything"
    
    	Dim lngCellCount As Long
    	lngCellCount = lngCountCellsOfAColor(KEYS_SHEET_NAME, _
                                         	KEYS_ALL_RANGE_NAME, _
                                         	BackgroundColor, _
                                         	HEADING_INTERIOR_COLORINDEX)
    
    
    	MsgBox "The interior color of the heading cells in sheet " _
           	& KEYS_SHEET_NAME & " is " & HEADING_INTERIOR_COLORINDEX & vbLf _
           	& lngCellCount & " cells are this color.", _
        	vbInformation, _
        	ActiveWorkbook.Name
    
    End Sub
    Listing 3 demonstrates that the counting function works.

    Code:
    Sub GetCellColor()
    
    	Const MY_SHEET_NAME As String = "KEYS"
    	Const ROWINDEX As Long = 1
    	Const COLINDEX As Integer = 1
    
    	Dim WhichPart As enmColorProperties: WhichPart = BackgroundColor
    
    	Const MAX_COLS As Integer = 256
    	Const MAX_ROWS As Long = 65536
    
    	If ROWINDEX > MAX_ROWS Then
        	MsgBox "The specified row index of " & ROWINDEX & " is too large." & vbLf _
               	& "The value must be a whole number between 1 and " & MAX_ROWS, _
            	vbExclamation, _
            	ActiveWorkbook.Name
    	Else
        	If COLINDEX > MAX_COLS Then
            	MsgBox "The specified column index of " & COLINDEX & " is too large." & vbLf _
                   	& "The value must be a whole number between 1 and " & MAX_COLS, _
                	vbExclamation, _
                	ActiveWorkbook.Name
        	Else
            	Dim strColorName As String
            	Dim fShowMe As Boolean
            	Dim wksThis As Worksheet
            	Dim varColor As Variant
    
            	Select Case WhichPart
                	Case FontColor
                    	fShowMe = True
                    	strColorName = "FontColor"
                    	Set wksThis = ActiveWorkbook.Worksheets(MY_SHEET_NAME)
                    	varColor = wksThis.Cells(ROWINDEX, COLINDEX).Font.ColorIndex
                	Case BackgroundColor
                    	fShowMe = True
                    	strColorName = "BackgroundColor"
                    	Set wksThis = ActiveWorkbook.Worksheets(MY_SHEET_NAME)
                    	varColor = wksThis.Cells(ROWINDEX, COLINDEX).Interior.ColorIndex
                	Case Else
                    	MsgBox "The specified value of variable WhichPart, " & WhichPart _
                           	& " is invalid. Valid values are 1 and 2.", _
                        	vbExclamation, _
                        	ActiveWorkbook.Name
            	End Select
    
            	If fShowMe = True Then
    
                	MsgBox "The " & strColorName & " of cell (" & ROWINDEX & ", " & COLINDEX & ") of worksheet " _
                    	& wksThis.Name & " in workbook " & ActiveWorkbook.FullName & " is " & varColor, _
                    	vbInformation, _
                    	ActiveWorkbook.Name
            	End If
        	End If
    	End If
    
    End Sub
    Listing 4 is a utility macro, for quickly identifying the colorindex of the cells of interest.

    Attached Files Attached Files
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A couple of functions to get the number of cells within a range with either the same background color as a reference cell, or with an absolute color value. As mentioned, the change of a background color doesn't trigger recalculation, so you have to force it with Ctrl-Alt-F9.
    Attached Files Attached Files
    This eco-post is made of recycled electrons

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The Chip Pearson functions should work. You don't really have to understand them to use them. You just have to add the functions to a module and then use them as described by Pearson...

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    ...wow....a lot of stuff going on here, and I thank you for your replies and help.

    I think that I might have found the answer (I hope).........I am using a free Excel add-in utility called ASAP-Utilities (designed by Bastien Mensink from, where else, the Netherlands...)...) I have used it for a couple of years and it is outstanding....after I posted my question, I thought that I should take a look at it for help. I discovered, early this morning, that the most recent upgrade now contains a 'utility' that counts cells by interior color.....you have to 'enter' the function using ASAP (for red cells, it would be =ASAPCOUNTBY CELLCOLOR(range,3) where 3 is from the color pallette.......because it is pasrt of ASAP, I imagine that you have to download and install ASAP and add it to Excel, but it works like a charm.....you do have to hit F9 to force the calculations, but that seems to be the case even with the most complex VBA/functions when it comes to counting colors........in my case, I want to color the cells manually and then count the reds or greens etc. There is also an ASAP formula that will count by font color.

    Neither of these seem to work if the cell/font is colored by conditonal formatting, but I haven't played with it enough to know if it will.....however, in the meantime, this works like a charm for me.........thanks, again, for your replies and I hope that my reply will help others.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Conditional formatting is different than explicit Chip Pearson has functions for reading those as wellhttp://www.cpearson.com/excel/CFColors.htm

    Steve


  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by dmcnab View Post
    Neither of these seem to work if the cell/font is colored by conditonal formatting
    If you are coloring cells by conditional formatting, you should be able to create a formula to count the colored cells.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the conditional formatting is consistent over the range, it would be more efficient to just test the cells content and compare to the condition to count the cells meeting the various conditions and ignore trying to figure out the cell's conditional color...

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Stephane Quenson View Post
    A couple of functions to get the number of cells within a range with either the same background color as a reference cell, or with an absolute color value. As mentioned, the change of a background color doesn't trigger recalculation, so you have to force it with Ctrl-Alt-F9.
    I see that your CountBackgroundColorFromRefCell and CountBackgroundColorFromColor functions take Range objects, and that they appear to work with simple, local range definitions. I've never been able to get them to work for me, but that may be because I usually use Named Ranges. How do they behave with Named Ranges, absolute addresses, and addresses of ranges in other sheets?
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  10. #10
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't know how the functions will work in such cases and cannot test it right now being away from my main computer but I guess it should work. If it doesn't, it's not my fault but Microsoft's fault to not make all ranges equal in functions!
    This eco-post is made of recycled electrons

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by dmcnab View Post
    I am looking for either VBA or a 'regular' COUNT or COUNTIF function (??) that I can use to count the number of cells, based on color
    I'm late to this party, and I don't think my suggestion fits your requirement, but in Excel 2007, Data | Filter will filter on color coding - you can then run =SUBTOTAL(2,<range>) to get the count from the specific filter applied. But you can only do one filter at a time.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by David Gray View Post
    How do they behave with Named Ranges, absolute addresses, and addresses of ranges in other sheets?
    The functions Stephane posted approximate typical Excel functions in that they can be fed a range reference (single cell or more).

    They won't work as written directly with Named Ranges; would have to use the same technique you use to convert the named range string .Range("MyNamedRange") to an object model range. They will work fine with absolute addresses, and addresses of ranges in other sheets if the other sheet range is correctly specified in the argument(s).
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by David Gray View Post
    How do they behave with Named Ranges, absolute addresses, and addresses of ranges in other sheets?
    They work well, at least in Excel 2003, I just tested the formulas: =CountBackgroundColorFromColor(MyNamedRange, 0), CountBackgroundColorFromColor($A$45:$F65,0), CountBackgroundColorFromColor(Sheet2!A1:B33,0) and they return proper results.
    This eco-post is made of recycled electrons

  14. #14
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by JohnBF View Post
    The functions Stephane posted approximate typical Excel functions in that they can be fed a range reference (single cell or more).

    They won't work as written directly with Named Ranges; would have to use the same technique you use to convert the named range string .Range("MyNamedRange") to an object model range. They will work fine with absolute addresses, and addresses of ranges in other sheets if the other sheet range is correctly specified in the argument(s).
    I thought so, because I spent many unbilled hours in 2003, trying to get a worksheet function to treat a named range as an object model range before I gave up and wrote rngRangeFrmAddr_P6C. I subsequently discovered the Names collection, but I've never gone back to see whether it could be used to simplify rngRangeFrmAddr_P6C, because, after all, it worked as written, and was easy enough to use in VBA code, which was my chief design goal for it. To date, I've never used it directly from a worksheet, although I've made some indirect use of it, in functions that were designed as custom worksheet functions.

    FWIW, attached archive, Excel\XLRangeLib_WW.ZIP contains a module, XLRangeLib_WW.BAS, which includes rngRangeFrmAddr_P6C, along with other functions that do such things as creating a new Range object from a rectangular region of an existing Range object. The archive also contains another module, WWXLAppExceptions.CLS, and a workbook, ErrWks.XLS, comprised of two worksheets, XLRangeLib_WW and wsWWXLAppErrors, To use the functions in XLRangeLib_WW.BAS, you must import the class module, WWXLAppExceptions.CLS, and one of the two worksheets, XLRangeLib_WW. The other sheet, wsWWXLAppErrors, was already in the workbook, so I left it as a second example of how to use the WWXLAppExceptions class. I didn't put the modules into a workbook, because I thought it would be easier to import them from the VBA module files, since there doesn't seem to be a good way to copy modules from one project to another in the VBA code editor.
    Attached Files Attached Files
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •