Macro Help - Array

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CSE with function returning an array? 2
Error with calling Format sub 7
Excel VBA 1
count cells with diagonal borders 3
WorksheetFunction.Match Usage? 9
if / then structure with macros 1
array output to cell range 5
Cell values to an array, then array transpose to another workbook 8

Top