Can anyone recommend a good formula for counting the cells of a common color
in a range?

We have a color-coded visual schedule, but need to count the cells of a
common color to determine if we have allocated enough resources to a
particular task. Please note that the assigned colors are related to a
certain task (in a legend) and are often custom colors not chosen directly
from the standard palette.

There are no in-built functions to do this.

However, Chip Pearson shows how it can be done with a bit of VBA he

http://www.cpearson.com/excel/colors.aspx

Hope this helps.

Pete

On Jan 28, 2:51*am, "Blue Max" wrote:
Can anyone recommend a good formula for counting the cells of a common color
in a range?

We have a color-coded visual schedule, but need to count the cells of a
common color to determine if we have allocated enough resources to a
particular task. *Please note that the assigned colors are related to a
certain task (in a legend) and are often custom colors not chosen directly
from the standard palette.

For counting only...................

Select the range of cells then EditFindFormatChoose from cellSelect a
cell from the custom colors legendOK

In "Found" dialog hit CTRL + a.

Right-click on Status Bar and "Count"


Gord Dibben MS Excel MVP

On Tue, 27 Jan 2009 19:51:16 -0700, "Blue Max" wrote:

Can anyone recommend a good formula for counting the cells of a common color
in a range?

We have a color-coded visual schedule, but need to count the cells of a
common color to determine if we have allocated enough resources to a
particular task. Please note that the assigned colors are related to a
certain task (in a legend) and are often custom colors not chosen directly
from the standard palette.

Thank you, Gord, this is an excellent way to get a quick manual count.
However, how do you incorporate this into a cell cell formula in Excel 2007
so that you can maintain a continual dynamic count in a cell on the
worksheet?

Thanks,
Richard

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
For counting only...................

Select the range of cells then EditFindFormatChoose from cellSelect a
cell from the custom colors legendOK

In "Found" dialog hit CTRL + a.

Right-click on Status Bar and "Count"


Gord Dibben MS Excel MVP

On Tue, 27 Jan 2009 19:51:16 -0700, "Blue Max"
wrote:

Can anyone recommend a good formula for counting the cells of a common
color
in a range?

We have a color-coded visual schedule, but need to count the cells of a
common color to determine if we have allocated enough resources to a
particular task. Please note that the assigned colors are related to a
certain task (in a legend) and are often custom colors not chosen directly
from the standard palette.

Thank you, Pete. This was a great recommendation that included additional
color functions that we might find useful down the road. Thank you for
sharing this information with us.

Sincerely,
Richard

************************
"Pete_UK" wrote in message
...
There are no in-built functions to do this.

However, Chip Pearson shows how it can be done with a bit of VBA he

http://www.cpearson.com/excel/colors.aspx

Hope this helps.

Pete

On Jan 28, 2:51 am, "Blue Max" wrote:
Can anyone recommend a good formula for counting the cells of a common
color
in a range?

We have a color-coded visual schedule, but need to count the cells of a
common color to determine if we have allocated enough resources to a
particular task. Please note that the assigned colors are related to a
certain task (in a legend) and are often custom colors not chosen directly
from the standard palette.

You have to use VBA

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long

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

Copy/paste the UDF to a general module in your workbook.

Usage is.................=COUNTBYCOLOR(range,3,FALSE)

3 is red

To see a list of colors and index numbers run this macro

Sub ListColorIndexes()
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Ndx
Next Ndx
End Sub


Gord

On Sat, 31 Jan 2009 11:56:47 -0700, "Blue Max" wrote:

Thank you, Gord, this is an excellent way to get a quick manual count.
However, how do you incorporate this into a cell cell formula in Excel 2007
so that you can maintain a continual dynamic count in a cell on the
worksheet?

Thanks,
Richard

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
For counting only...................

Select the range of cells then EditFindFormatChoose from cellSelect a
cell from the custom colors legendOK

In "Found" dialog hit CTRL + a.

Right-click on Status Bar and "Count"


Gord Dibben MS Excel MVP

On Tue, 27 Jan 2009 19:51:16 -0700, "Blue Max"
wrote:

Can anyone recommend a good formula for counting the cells of a common
color
in a range?

We have a color-coded visual schedule, but need to count the cells of a
common color to determine if we have allocated enough resources to a
particular task. Please note that the assigned colors are related to a
certain task (in a legend) and are often custom colors not chosen directly
from the standard palette.

Thank you, Gord. You may also be interested in the link provided by Pete_UK
in this same thread. If provides some great insight into the color
functions.

Thanks,
Richard

******************
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You have to use VBA

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long

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

Copy/paste the UDF to a general module in your workbook.

Usage is.................=COUNTBYCOLOR(range,3,FALSE)

3 is red

To see a list of colors and index numbers run this macro

Sub ListColorIndexes()
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Ndx
Next Ndx
End Sub


Gord

On Sat, 31 Jan 2009 11:56:47 -0700, "Blue Max"
wrote:

Thank you, Gord, this is an excellent way to get a quick manual count.
However, how do you incorporate this into a cell cell formula in Excel
2007
so that you can maintain a continual dynamic count in a cell on the
worksheet?

Thanks,
Richard

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
For counting only...................

Select the range of cells then EditFindFormatChoose from cellSelect
a
cell from the custom colors legendOK

In "Found" dialog hit CTRL + a.

Right-click on Status Bar and "Count"


Gord Dibben MS Excel MVP

On Tue, 27 Jan 2009 19:51:16 -0700, "Blue Max"
wrote:

Can anyone recommend a good formula for counting the cells of a common
color
in a range?

We have a color-coded visual schedule, but need to count the cells of a
common color to determine if we have allocated enough resources to a
particular task. Please note that the assigned colors are related to a
certain task (in a legend) and are often custom colors not chosen
directly
from the standard palette.

Well, thanks for feeding back, but Chip is the one you should thank
really.

Pete

On Jan 31, 7:44*pm, "Blue Max" wrote:
Thank you, Pete. *This was a great recommendation that included additional
color functions that we might find useful down the road. *Thank you for
sharing this information with us.

Sincerely,
Richard

Thanks


Gord

On Sat, 31 Jan 2009 13:20:54 -0700, "Blue Max" wrote:

Thank you, Gord. You may also be interested in the link provided by Pete_UK
in this same thread. If provides some great insight into the color
functions.

Thanks,
Richard

******************
"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
You have to use VBA

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long

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

Copy/paste the UDF to a general module in your workbook.

Usage is.................=COUNTBYCOLOR(range,3,FALSE)

3 is red

To see a list of colors and index numbers run this macro

Sub ListColorIndexes()
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Ndx
Next Ndx
End Sub


Gord

On Sat, 31 Jan 2009 11:56:47 -0700, "Blue Max"
wrote:

Thank you, Gord, this is an excellent way to get a quick manual count.
However, how do you incorporate this into a cell cell formula in Excel
2007
so that you can maintain a continual dynamic count in a cell on the
worksheet?

Thanks,
Richard

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
For counting only...................

Select the range of cells then EditFindFormatChoose from cellSelect
a
cell from the custom colors legendOK

In "Found" dialog hit CTRL + a.

Right-click on Status Bar and "Count"


Gord Dibben MS Excel MVP

On Tue, 27 Jan 2009 19:51:16 -0700, "Blue Max"
wrote:

Can anyone recommend a good formula for counting the cells of a common
color
in a range?

We have a color-coded visual schedule, but need to count the cells of a
common color to determine if we have allocated enough resources to a
particular task. Please note that the assigned colors are related to a
certain task (in a legend) and are often custom colors not chosen
directly
from the standard palette.