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

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

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.

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

First............the cell gets its red color from you manually
formatting that cell?

Or does it get red color due to Conditional Formatting?

Big difference in what code or method is used to count.

From your example formula that used to work I would say that the cells
were manually colored.

Don's function with a modification for background color will work for
these types of cells but not if CF was used for coloring.

Function CountColor(r As Range) As Long
For Each r In r.Cells
CountColor = CountColor + IIf(r.Interior.ColorIndex = 3, 1, 0)
Next
End Function

To install the code in your workbook..........................

With your WB open, hit Alt + F11 to open Visual Basic Editor.

Hit ctrl + r to open Project Explorer.

Expand your WB tree by clicking on the "+" sign.

On Menu above hit InsertModule.

Paste the code into that module.

Alt + q to return to Excel window.

In cell enter the formula =CountColor(A1:C3) adjust for range.

Note the index of Red is 3, not 2 as in your original SUMPRODUCT
formula.


Gord

On Sat, 16 Jul 2011 16:50:25 +0100, "Rob"
wrote:

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

Thanks Gord

The cells are conditionally formatted so that when I enter text, ie a name
or a place, the background automatically fills with red.

What I am trying to achieve is, on page of several hundred cells, how many
are red, ie how many have text entered into them.

I think the answer below will not work with the CF, right.

Regards Rob




"Gord" wrote in message
...
First............the cell gets its red color from you manually
formatting that cell?

Or does it get red color due to Conditional Formatting?

Big difference in what code or method is used to count.

From your example formula that used to work I would say that the cells
were manually colored.

Don's function with a modification for background color will work for
these types of cells but not if CF was used for coloring.

Function CountColor(r As Range) As Long
For Each r In r.Cells
CountColor = CountColor + IIf(r.Interior.ColorIndex = 3, 1, 0)
Next
End Function

To install the code in your workbook..........................

With your WB open, hit Alt + F11 to open Visual Basic Editor.

Hit ctrl + r to open Project Explorer.

Expand your WB tree by clicking on the "+" sign.

On Menu above hit InsertModule.

Paste the code into that module.

Alt + q to return to Excel window.

In cell enter the formula =CountColor(A1:C3) adjust for range.

Note the index of Red is 3, not 2 as in your original SUMPRODUCT
formula.


Gord

On Sat, 16 Jul 2011 16:50:25 +0100, "Rob"
wrote:

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