Here the steps to create the count cell color UDF:
- Open Microsoft Excel then press Alt+F11 to show Visual Basic Editor window.
- 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
- Close VBE window and back to Excel.
- To test the UDF, create some example data, or you can download this example file here.
- 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
- Press Enter and in cell F2 the result is 6. It means the number of cells with Blue cell color is 6 cells.
- 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.
- 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
- Fill the name that you want named to at the File Name box. Here I name it Count Cell Color.
- For the file type, choose Excel Add-In (.xlam) format.
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
- 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.
- On the Add-In dialog box, click Browse button so Browse dialog box is displayed.
- Go to file location that Add-In file is saved. Choose the file and then click Open.
- On the Add-Ins dialog box make sure the add-in checkbox is unchecked. Then click OK.