Macro Help - Array

  • Thread starter Thread starter rk0909
  • Start date Start date
R

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
 
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
 
Back
Top