Macro Help - Array

[rk0909]

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

 

[Bob Phillips]

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

 

[Chip Pearson]

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

 

[rk0909]

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