tidying up VBA



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)
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

Bob Phillips


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
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.



(remove nothere from the email address if mailing direct)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question