Identifying a shaded cell

[Stclements1]

I have a gantt chart that I want to be able to evaluate the number of activity days within a row.
Example
cells b3:k3 there are 8 days of activity those days would be highlighted with a colour (say green for example), in cell m3 I want to write a formula that evaluates all the cells between b3:k3 and counts only the coloured ones and returns that number in this case 8. I tried subtotal and countif as well as conditional formatting but unfortunately all to no avail. I would very much appreciate some guidance thank you.

 

[Fluff]

Are you manually changing the cell colour?
Also is there anything in the cells that show it's one of the activity days?

 

[Stclements1]

I manually input the colour in the cells that represent activity days, other than the colour that I put in the cell there is no other data within the cell. I do this because there could be as many as 400 cells in a row and maybe as many rows and to count manually which ones are coloured is time-consuming so I just want to have a formula that counts only coloured cells in each row. what ultimately it does is takes all the cells and by counting the coloured cells across the whole sheet informs me the percentage of activity days within that given timeframe.

 

[Fluff]

In that case rather than changing the fill colour manually why not put a 1 or an X (or something else) into the cell.
That way you can use a countifs formula in M3 & have conditional formatting to change the actual colour of the cell.

 

[Stclements1]

because the colour is much more visible as a portion of time whilst I understand that simply adding numerics would be simple it doesn't have the visibility that a string of coloured cells does and thus one can have a feel simply looking at the chart as to the time periods.

 

[Fluff]

because the colour is much more visible as a portion of time

Which is why I suggested using conditional formatting to add the colour.
Colour should NEVER be used as data, just for highlighting information.

 

[cmowla]

For what it's worth, you can use this user-defined function (UDF) to count the number of colored cells in a range. It will slow things down, so it's better with what you have chosen to do, but for entertainment purposes:

Function COLOREDCELLS(ByRef rng As Range)
Application.Volatile
Dim r As Range, numberOfColoredCells As Integer
numberOfColoredCells = 0
For Each r In rng
    If Evaluate("Helper(" & Application.Caller.Parent.Name & "!" & r.Address & ")") <> 16777215 Then numberOfColoredCells = numberOfColoredCells + 1
Next r
COLOREDCELLS = numberOfColoredCells
End Function
Function Helper(r As Range)
Helper = r.Interior.Color
End Function

a.xlsb
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5		8
6
Sheet10

Cell Formulas
Range	Formula
B5	B5	=COLOREDCELLS(B3:K3)

And if it was color that was the result of conditional formatting, use this helper function instead:

Function Helper(R As Range)
Helper = R.DisplayFormat.Interior.Color
End Function

 

[Stclements1]

I very much appreciate your help I have now managed to solve the issue. Many Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Fluff]

For what it's worth, you can use this user-defined function (UDF) to count the number of colored cells in a range.

Except that will not update if the user simply changes the colour of the cell, which is a permanent problem with this type.