Count cells based on color
I had made an Excel Sheet, and I had colored the cells for status. Like Red=Bad, Green=Good, Yellow=ToBeConfirmed, etc.
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
Application.Volatile
For Each n In myrange
If n.Interior.ColorIndex = colour.Interior.ColorIndex Then
FNcountcolor = FNcountcolor + 1
End If
Next
End Function
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).
=FNcountcolor($A$1:$X$20,C2)
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
Application.Volatile
For Each n In myrange
If n.Interior.ColorIndex = colour.Interior.ColorIndex Then
FNcountcolor = FNcountcolor + 1
End If
Next
End Function
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).
=FNcountcolor($A$1:$X$20,C2)
Where...
A1:X20 is the range from where you want to count
C2 is a cell with the colour you want to count.
Verify...
:)
Note:
- 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".
Hi this is agreat piece of VBA....but do you have anything for counting a cell colour when its conditionaly formatted to a colour?
ReplyDeleteWell...
DeleteI thought this code would work like charm... Seems it does NOT works with conditional formatting.
Thanx for pointing out this thing. I shall post the new code if I succeed.
Adil,
ReplyDeleteDoesn't look like there is a VBA code out there for this....if you do manage to come up with anything, then I will be really impressed!!! - not to mentioned grateful!!!!!