Ben,

You could place the following two functions in a module (same place you
would typically store a macro. Then, you could use the functions in your
workbook the same as you use Excel's fundtions. For example, if you wanted
a count of all the strings separated by commas found in A1:D50 and a count
of all the unique font colors appearing in the same range,
=ModelCount(a1:d50)
would give the number of strings and
=ColorCount(a1:d50)
would give the count of how many different colors were used in the same
range.

'---------------------------------------------------

Function ModelCount(myRange As Range) As Integer
Dim rngCell As Range
Dim intCount As Integer

intCount = 0
For Each rngCell In myRange
If Len(rngCell.Text 0) Then
intCount = intCount + UBound(Split(rngCell.Text, ",")) + 1
End If
Next rngCell

ModelCount = intCount

End Function



Function ColorCount(theRange As Range) As Long
Dim rgnCell As Range
Dim txtColor As Variant

Set clrDict = CreateObject("Scripting.Dictionary")

For Each rngCell In theRange
If Len(rngCell.Text 0) Then
For x = 1 To Len(rngCell.Text)
txtColor = rngCell.Characters(x, x).Font.Color
If Len(txtColor) 0 Then
If Not clrDict.Exists(txtColor) Then
clrDict.Add txtColor, txtColor
End If
End If
Next x
End If
Next rngCell

ColorCount = clrDict.Count

Set clrDict = Nothing

End Function


'--------------------------------------------------

Steve Yandl



"Ben in CA" wrote in message
...
I haven't got this figured out yet.

I'd be open to using a complex macro if required - if anyone has any
ideas.

Thanks,

Ben

"Ben in CA" wrote:

Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model
numbers,
and the values are separated by commas and spaces in each cell. (eg.
8890,
x3340, mx750)

To make things more confusing, each model is colored differently
depending
on the distributor, and there are several different colors in some cells.
In
total, about six different colors are used (green, red, blue orange,
gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben