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.

 

[cmowla]

@Stclements1 ,
Again, for what it's worth, here's a better alternative to the user-defined function that won't slow down the sheet (at least not that much) and doesn't require recalculating the sheet. (But it will disable your ability to undo actions in this specific sheet! Take note of that.)

Right click on the sheet tab, and select View Code. And put the following VBA code in the window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range, counter As Integer, s() As String
s = Split(Split(Replace(Range("A1").Formula, ")", "("), "(")(1), ",")
counter = 0
For Each r In Range(s(0))
    If r.Interior.Color <> 16777215 Then counter = counter + 1
Next r
Range(s(1)).Value = counter
End Sub

By the code, you can see that Cell A1 is the "cell of focus". You can change it to whatever cell that you wanted to put the formula that counts the number of colored cells.

Now for the "formula" to put in that cell (as of right now, you need to select Cell A1 and do the following),

1. Type
   =SUM(
   
   
2. Select the (rectangular) range that you want to monitor how many of them are shaded. So if you select B3:J3, your formula will currently be:
   =SUM(B3:J3
   
   
3. Hold down Ctrl and select the cell that you would like to receive the actual number of shaded cells in the region you selected above. Let's say it's Cell A2.
   =SUM(B3:J3,A2
   
   
4. Now close the parenthesis and press enter.
   =SUM(B3:J3,A2)

Now whenever you change the color of the cells in B3:J3, all you have to do to have the number of shaded cells update in Cell A2 is to simply click once anywhere on the sheet. I gather that this may be an acceptable "substitute" in this situation, because you are manually changing the colors anyway. (It's not the same as a formula, but it's the second best thing to it.) So just remember to press one of the arrow keys, tab, enter, or simply click on any random cell in the sheet, and the number of colored cells in that range will update.