Being a Project Manager sometimes you have to do things that you didn’t know existed. There are somethings which are possible in a way, but sometimes they need to be done differently.
Today came a problem to me (A problem for me, an obvious something to have for the Manager). In a table containing different teams, I had to count the number of Engineers working in Development and Validation for each team.
Not a mean task if the list is small. Not mean when they are set in different sections. Not so mean even if you can use a table’s filter. But it is a mean task, when they’re in the same column and only differentiated by the color of the cell!
Different colors for depicting team-division is good idea. Dynamic counting based on color was not easy to do. Dynamic was anyways important because a team can always increase or decrease in size. It always does, doesn’t it?
On my search for a solution, I found out, which I had actually feared, that there’s no way you could use an excel formula to find color of the cell. EXCEPT…. using a VBA script. So I went for search of a script to let me count the number of cell in a range with a certain cell color.
I found a few, which invariably linked to either 1 of the 2 links on the Internet. I tried them or whatever different variant I could find, but it just didn’t work. That is UNTIL I landed on a different kind of solution to it (doesn’t work) (too complicated)
What worked is:
Public Function cell_colour(Cell_Check As Range) As Long
cell_colour = Cell_Check.Interior.Color
Public Function count_colour(Count_Range As Range, Colour As Long) As Long
Dim x As Range
count_colour = 0
For Each x In Count_Range
If x.Interior.Color = Colour Then
count_colour = count_colour + 1
from forum on edugeek.
In the excel cell where you want to get the count, use
Best part is, it is dynamic!
There’s a new Developer in Team B. Go to the colored cell of Lead Developer of Team B, click on Format painter, and then click on new member’s name.
Voila! The new member’s cell changes color and you get the updated numbers.