udf to count colour in a matrix

[uk747]

have a udf to count a colour
=Countcolour($A$1:$M$13,U$1)

what if i want to add a wider range , i.e.
B1:M1,C2:M2,D3:M3,E4:M4,F5:M5,G6:M6,H7:M7,I8:M8,J9:M9,K10:M10,L11:M11,M12

 

[pgc01]

Hi

It would help if you post the udf. I'm sure there are several udf's with that name available on the web.

 

[uk747]

Hi

It would help if you post the udf. I'm sure there are several udf's with that name available on the web.

was copied from elsewhere

Function Countcolour(rng As Range, colour As Range) As Long
Dim c As Range
Application.Volatile
For Each c In rng
    If c.Interior.ColorIndex = colour.Interior.ColorIndex Then
        Countcolour = Countcolour + 1
    End If
Next
End Function

 

[Rick Rothstein]

Just pass the range you indicated encased in a set of parentheses...

=Countcolour((B1:M1,C2:M2,D3:M3,E4:M4,F5:M5,G6:M6,H7:M7,I8:M8,J9:M9,K10:M10,L11:M11,M12),A2)

 

[uk747]

Just pass the range you indicated encased in a set of parentheses...

=Countcolour((B1:M1,C2:M2,D3:M3,E4:M4,F5:M5,G6:M6,H7:M7,I8:M8,J9:M9,K10:M10,L11:M11,M12),A2)

thanks Rick, i did manage to do it with formula below but yours is alot better

=Countcolour(B$1:M$1,Y$1) + Countcolour(C$2:M$2,Y$1) + Countcolour(D$3:M$3,Y$1) + Countcolour(E$4:M$4,Y$1) + Countcolour(F$5:M$5,Y$1) + Countcolour(G$6:M$6,Y$1) + Countcolour(H$7:M$7,Y$1) + Countcolour(I$8:M$8,Y$1) + Countcolour(J$9:M$9,Y$1) + Countcolour(K$10:M$10,Y$1) + Countcolour(L$11:M$11,Y$1) + Countcolour(M$12,Y$1)

 

[uk747]

just noticed one sligt problem when i run a macro to clear the cells colours the formula doesnt update until i click on F9

i did add Application.Calculation = xlAutomatic but itt didnt need it before