Count by Color on different worksheet

[Mike]

Hello,
Using Excel XP.
Earlier in the group someone asked about =SUM colored cells and was referred
to cpearson.com/excel/colors.htm. I went to the page and found the function
to count the colored cells within a range (which I want to do). This is the
code:

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

The function works for me if the range is on the same sheet.
=COUNTBYCOLOR(A1:A10,3,FALSE) When I have the formula on one sheet referring
to another sheet in the workbook it does not work (gives a value of always
zero.) Can anyone help me make the formula work if the range is on another
sheet. Thanks in advance.

Mike

 

[JulieD]

Hi Mike

=CountByColor(Sheet2!A1:A10,3,FALSE)

works for me
however, you might need to click on the cell with the formula in it and
press F9 to force a recalc of the formula if you change the number or colour
of the cells.

 

[Bob Phillips]

This worked for me

=COUNTBYCOLOR(Sheet2!A1:A10,3,FALSE)

--

HTH

RP
(remove nothere from the email address if mailing direct)