Thanks to both for responding.
However, I am not really sure what you are talking about as I am not that
Excell literate!! It's all a little over my head which, I suppose is why I
posed the question in the first place.
I have looked at Chip Pearsons page but it is mind boggling to me at this
stage as I am just trying to learn a little more. I can see certain things
but I don't know anything about modules, macros or functions.
For example, "how" do I place a macro into a "regular" module. What are
they?? What do I physically have to do to get this to work.
As stated, I am in no way an expert at this, I am just on the first step of
the ladder.
Thanks again
Regards, Rob
"Don Guillett" wrote in message
...
On Jul 15, 6:05 pm, Gord wrote:
ColorIndex is not a native Excel function.
I would suggest you had an add-in or personal.xls with that UDF
When you upgraded, you did not bring that with you.
See Chip Pearson's site for some code and a downloadable workbook with
all the colorindex functions.
http://www.cpearson.com/excel/Colors.aspx
Gord Dibben Microsoft Excel MVP
On Fri, 15 Jul 2011 23:27:19 +0100, "Rob"
wrote:
Hi
I have a table where I want to record any number of items that I pass on
to
people by putting their initials into a cell..
Whenever I put the initials of a person into a cell, I want the
background
colour for the cell to change to red and then have a "Total" cell which
adds
up how many cells have changed colour, ie how many items have I passed
on.
JP TC RH BJ SH Total
5
I have used in the past the following
=SUMPRODUCT(--(ColorIndex(B2:G3)=2)) which worked ok for a while but now
I
cannot get it to function properly.
Also I have upgraded to Office 2010 so is this why it will not work.
Any help appreciated.
Regards, Rob
Please insert the code below, Example:
=CountColor(A1:C3)
Peter
Place this macro in a REGULAR module. then use the formula
Function CountColor(r As Range) As Long
For Each r In r.Cells
CountColor = CountColor + IIf(r.Font.ColorIndex = 3, 1, 0)
Next
End Function