Count cells based on color
Now after spending long time, I wanted to count number of cells based on color. I googled a lot, and there was no single function to do this based on color. If I had used some text instead of color, life would have been much easier. Anyways, there is a small workaround to do this. As Excel does NOT have any native way of doing this, we must create our own custom function to do it.
We will first define our custom function. Press ALT+F11 to open vb editor, right-click "This Workbook" and "Insert" --> "Module" and paste the code below in there (no changes needed).
Function FNcountcolor(myrange As Range, colour As Range) As Long
Dim n as Range
For Each n In myrange
If n.Interior.ColorIndex = colour.Interior.ColorIndex Then
FNcountcolor = FNcountcolor + 1
Close VB editor.
Now, use the following function to do the counting based on color in one of the cell in your worksheet (Modify your range and your cell (for reference color) in the function below).
A1:X20 is the range from where you want to count
C2 is a cell with the colour you want to count.
- Now if you try to save the workbook, it cannot be saved as regular Excel Workbook. When the error is prompted, click NO, and then save the file as "Excel Macro-Enabled Workbook (*.xlsm)".
- You will also get a Privacy warning message prompt. Click "OK" as usual...
- When you reopen the new file (with extension .xlsm), you will have Macros Disabled message at top. Just click "Enable Macros".