I have this simple function to capture back ground color of a cell.

Function BGCol(refRange As Range)
BGCol = refRange.Interior.ColorIndex
End Function

I want to convert this to an array function where that array can store
colorindex for all the cells in a specified range and then i can use it in a
sumproduct formula to fins cells with a certain color.

Any help or direction appreciated.

Best,

RK

Take a look at http://www.xldynamic.com/source/xld.ColourCounter.html, that
is exactly what I do there.

--

HTH

Bob

"rk0909" wrote in message
...
I have this simple function to capture back ground color of a cell.

Function BGCol(refRange As Range)
BGCol = refRange.Interior.ColorIndex
End Function

I want to convert this to an array function where that array can store
colorindex for all the cells in a specified range and then i can use it in
a
sumproduct formula to fins cells with a certain color.

Any help or direction appreciated.

Best,

RK

Try

Function BGColors(RR As Range) As Long()
Dim Arr() As Long
Dim RNdx As Long
Dim CNdx As Long
ReDim Arr(1 To RR.Rows.Count, 1 To RR.Columns.Count)
For RNdx = 1 To RR.Rows.Count
For CNdx = 1 To RR.Columns.Count
Arr(RNdx, CNdx) = RR(RNdx, CNdx).Interior.ColorIndex
Next CNdx
Next RNdx
BGColors = Arr
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Thu, 25 Mar 2010 15:20:03 -0700, rk0909
wrote:

I have this simple function to capture back ground color of a cell.

Function BGCol(refRange As Range)
BGCol = refRange.Interior.ColorIndex
End Function

I want to convert this to an array function where that array can store
colorindex for all the cells in a specified range and then i can use it in a
sumproduct formula to fins cells with a certain color.

Any help or direction appreciated.

Best,

RK

works exactly as i wished. awesome!
thanks so much.

"Chip Pearson" wrote:

Try

Function BGColors(RR As Range) As Long()
Dim Arr() As Long
Dim RNdx As Long
Dim CNdx As Long
ReDim Arr(1 To RR.Rows.Count, 1 To RR.Columns.Count)
For RNdx = 1 To RR.Rows.Count
For CNdx = 1 To RR.Columns.Count
Arr(RNdx, CNdx) = RR(RNdx, CNdx).Interior.ColorIndex
Next CNdx
Next RNdx
BGColors = Arr
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Thu, 25 Mar 2010 15:20:03 -0700, rk0909
wrote:

I have this simple function to capture back ground color of a cell.

Function BGCol(refRange As Range)
BGCol = refRange.Interior.ColorIndex
End Function

I want to convert this to an array function where that array can store
colorindex for all the cells in a specified range and then i can use it in a
sumproduct formula to fins cells with a certain color.

Any help or direction appreciated.

Best,

RK
.