ColorIndex formula that works with Ranges

[BMcHale]

Hi I'm using the colorindex() formula in a work book, but i would like it to work with a sumproduct() formula aswell, for instance;

Where the color index of green is 14 and yellow is 6

=SUMPRODUCT((COLORINDEX(A1:A5)=14)*(B1:B5))
Where

(Green)	15
(Green)	27
(Yellow)	62
(Green)	38
(Yellow)	47

<tbody>
</tbody>

So
=SUMPRODUCT(({14,14,6,14,6}=14)*{15,27,62,38,47})
=SUMPRODUCT({1,1,0,1,0}*{15,27,62,38,47})
=SUM(15,27,0,38,0)
=80

However when I use the range brackets, (Ctrl, Shift, Enter) it doesn't work.

Is there any way to edit the formula using VBA to allow it to work like this instance.

Thanks,
Billy

 

[RoryA]

Use Font.colorindex in the code instead of interior.colorindex