Excel + VB

[army20]

Hi,

Thank you for taking the time to read and help !

I've found how to do what I need to do on this webpage here. The only thing is That I don't figure out how to make it work.

Here's the code:

Function CountByColor(InRange As Range, _

WhatColorIndex As Integer, _

Optional OfText As Boolean = False) As Long

'

' This function return the number of cells in InRange with

' a background color, or if OfText is True a font color,

' equal to WhatColorIndex.

'

Dim Rng As Range

Application.Volatile True

For Each Rng In InRange.Cells

If OfText = True Then

CountByColor = CountByColor - _

(Rng.Font.ColorIndex = WhatColorIndex)

Else

CountByColor = CountByColor - _

(Rng.Interior.ColorIndex = WhatColorIndex)

End If

Next Rng

End Function

They says on the website that I can call the fonction using a formula like this one:

=COUNTBYCOLOR(A1:A10,3,FALSE)

I do not know if the code is at the right place (see print screen):

I call the formula this way:

And I get this error:

Can someone help me plz

[jaclaz]

It looks right.

I don't have Excel installed, so I cannot try it directly, but first thing (as you need - from the other thread to just count cell interiors, NOT text) would be to simplify the function:

Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long

'

' This function return the number of cells in InRange with

' a background color equal to WhatColorIndex.

'

Dim Rng As Range

Application.Volatile True

For Each Rng In InRange.Cells

CountByColor = CountByColor - (Rng.Interior.ColorIndex = WhatColorIndex)

Next Rng

End Function

But I am failing to see how the above works (CountByColor is not initialised)....

You can try the following (untested as said above):

Function CountByColor(InRange As Range, WhatColorIndex As Integer) As Long

'

' This function return the number of cells in InRange with

' a background color equal to WhatColorIndex.

'

Dim Rng As Range

Application.Volatile True

CountByColor = 0

For Each Rng In InRange.Cells

IF Rng.Interior.ColorIndex = WhatColorIndex Then

CountByColour = CountByColour + 1

End If

Next Rng

End Function

jaclaz

Edited March 5, 2006 by jaclaz

[army20]

it's not working