Excel - probably easy 1

[rossmcl]

Hi
I want to find out how many cells in a named range (basically 60 cells in a row) have background colour red (Selection.Font.Interior.ColorIndex = 3)

Is there an more efficient way of checking this than using Offset for each cell?

Thanks in Advance
Much Appreciated
rossmcl

 

[LoNeRaVeR]

rossmcl:

That's a very good question, and yes, there is a much easier and less time consuming way than looping through all the cells.
[tt]
Sub CountRedCells()
Dim iCount As Integer
Dim oCell As Object
For Each oCell In Selection
If oCell.Interior.ColorIndex = 3 Then iCount = iCount + 1
Next oCell
End Sub
[/tt]
Don't forget to set your selection prior to this bit of code.

Hope that helps,

LoNeRaVeR

 

[LoNeRaVeR]

rossmcl:

You probably know this, but the [tt]If[/tt] statement needs to either be on one line or split as follows:
[tt]
If oCell.Interior.ColorIndex = 3 Then
iCount = iCount + 1
End If
[/tt][/tt]
LoNeRaVeR