Count number only if cell is background filled

[StevieQ]

Hi

Hope someone can help.

I need a formula to count the number of times a number appears in a background filled cell, ignoring the occasions when it appears in cells with no background fill.
Say the number is to be entered in cell T3, and the range to examine is L6:EM179, and the cell Colour Index is 37 (Dark Blue in Excel 2010), I’ve tried the following array formula:

{=COUNT(IF(GetColor(L6:EM179)=37,IF(L6:EM179=T3,1,””),””))}

Where GetColor is set up as a VBA User Defined Function using the following code cribbed off the Internet:

Function GetColor(Mycell As Range)
GetColor = Mycell.Interior.ColorIndex
End Function

Unfortunately, this doesn’t appear to work, as it just returns a zero result.

Any help would be greatly appreciated.

Thanks

[ChemistB]

The UDF's are not automatically running as Excel works it's way through the array so you end up with 0.

Best bet is to set up a second sheet and in the same range set up a range of cells with
=GetColor(sheet1!L6) (for example)
Then you can use COUNTIFS($L$6:$EM$179, $T$3, Sheet2!$L$6:$EM$179, 37)

[StevieQ]

Thanks ChemistB

I'll check out your suggestion when I'm back at work and can access my spreadsheet!

Cheers

[StevieQ]

Had a chance now to follow your advice, and it seems to work great, although I do have to perform a Ctrl+Alt+F9 every time I change a colour to get it to re-calculate.

Thanks for your help.