Counting Coloured Cells

[na gCopaleen]

I have a timetable in Excel.

People put in their holidays by filling a cell with the colour red for
times when they will be off.

I'd like to be able to add up (CountIf ?) all the red cells in a row
and get the number of days that the person is off.

I've tried:
---------------------------
Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function
--------------------
But when I call it via

=countbycolor(D11:H11,4, FALSE)

I get nothing but #NAME

Anyone help

 

[Bob Phillips]

As before it works fine for me (except that red is 3).

You do know that even with Application.Volatile it won't recalculate if a
cell changes colour, it will need a forced recalculation?

 

[Dave Peterson]

Sometimes you can do everything correct--except put the code in the wrong spot.

This kind of code goes into a General Module--not behind a worksheet and not
under ThisWorkbook.

And if you put it in a general module in a different workbook, make sure you use
it correctly in the cell:

=countbycolor(a1:a10,3)
could be:
=myotherworkbook.xls!countbycolor(a1:a10,3)

(Myotherworkbook.xls has to be open for this to work, too.)