I would really really appreciate it if someone could take a look at this and
asee if there is a way of making the VBA quickwer, at the moment this runs
like a dog!!

-------------------------------------------------------

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

--------------------------------------------------------------------

Thank you very much everone. I do appreciate your help.

Merry Xmas

Chris,

It is so simple there is not a lot of actual coding will make a difference.
I also found it relatively quick on a range of 14 columns, with many
formulae (13 secs)

Are you selecting large ranges, that are not specific, such as multiple
columns? If so, could you be m,ore specific with the ranges

I did reduce it fractionally with this code

If OfText = True Then
If Rng.Font.ColorIndex = WhatColorIndex Then
CountByColor = CountByColor + 1
End If
Else
If Rng.Interior.ColorIndex = WhatColorIndex Then
CountByColor = CountByColor + 1
End If
End If

You do know as well don't you that if you change a cell to the target
colour, it won't automatically recalculate, it will need to be forced.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris" wrote in message
...
I would really really appreciate it if someone could take a look at this
and
asee if there is a way of making the VBA quickwer, at the moment this runs
like a dog!!

-------------------------------------------------------

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

--------------------------------------------------------------------

Thank you very much everone. I do appreciate your help.

Merry Xmas