Count The Number of Cells With Specific Cell Color By Using VBA

Article translations Article translations
Article ID: 2815384 - View products that this article applies to.

About Author:

Collapse this tableExpand this table
Collapse this imageExpand this image
MVP
This article is provided by MVP Raddini Rahayu. Microsoft is so thankful that MVPs who proactively share their professional experience with other users. The article would be posted on MVP's website or blog later.
Expand all | Collapse all

On This Page

Symptoms

On the Microsoft Excel Formula, we know that in Statistical category there is a function to counts the number of cells within a range that meet the give condition, called COUNTIF. Criteria on that function can be formatted as text or number. But in fact, it could be not only text or number, but also cell colors. Then, how we can get the results with that criteria while COUNTIF function can not be the solutions.

Collapse this imageExpand this image
2815473

Resolution

Altough without using COUNTIF function, we can still get the results with using VBA. With VBA, we can create a UDF (User Defined Function) and save it into Add-In file so it can be used to every workbook and other computer.


What To Do

Here the steps to create the count cell color UDF:

  1. Open Microsoft Excel then press Alt+F11 to show Visual Basic Editor window.
  2. On Insert menu, select Module to create a module. Then write the following script:
    Function CountCcolor(range_data As range, criteria As range) As Long
        Dim datax As range
        Dim xcolor As Long
    xcolor = criteria.Interior.ColorIndex
    For Each datax In range_data
        If datax.Interior.ColorIndex = xcolor Then
            CountCcolor = CountCcolor + 1
        End If
    Next datax
    End Function
    Collapse this imageExpand this image
    2815471

  3. Close VBE window and back to Excel.
  4. To test the UDF, create some example data, or you can download this example file here.
  5. At cell D3, write the function: =CountCcolor(range_data,criteria)
    in range_data argumen, select cell C2 to C51
    in criteria argumen, select cell F1

    Collapse this imageExpand this image
    2815474

  6. Press Enter and in cell F2 the result is 6. It means the number of cells with Blue cell color is 6 cells.

    Collapse this imageExpand this image
    2815475

  7. You can also test another color. Change the color in cell F1 with any color you want from the data by using Format Painter to get same color.

    Collapse this imageExpand this image
    2815476

  8. You can also pack the UDF, so that function can be used in another workbook and machine. Please following this steps :


    Step 1: Save The Workbook

    1. Fill the name that you want named to at the File Name box. Here I name it Count Cell Color.
    2. For the file type, choose Excel Add-In (.xlam) format.

      Collapse this imageExpand this image
      2815479


      Note: You can save your Add-In file anywhere you want. But if you want it to be listed on Excel built-in, you should save it into the default location. On my computer with Windows 7 operating system, the default location for any versions of Microsoft Excel is: C:\Users\RADDINI\AppData\Roaming\Microsoft\AddIns


    Step 2: Install the Add-In

    1. Open Microsoft Excel on computer that you want install the Add-In. Open Add-Ins dialog box by clicking Add-In on the Developer tab.
    2. On the Add-In dialog box, click Browse button so Browse dialog box is displayed.

      Collapse this imageExpand this image
      2815496

    3. Go to file location that Add-In file is saved. Choose the file and then click Open.
    4. On the Add-Ins dialog box make sure the add-in checkbox is unchecked. Then click OK.

      Collapse this imageExpand this image
      2815497
Now the Count Cell Color UDF has installed and ready to use.

Community Solutions Content Disclaimer

MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.

Properties

Article ID: 2815384 - Last Review: September 25, 2013 - Revision: 2.1
Applies to
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
  • Microsoft Excel 2013
Keywords: 
kbmvp kbcommunity kbstepbystep KB2815384

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com