EXCEL VBA QUESTION - PRETTY SIMPLE I SUSPECT

[rossmcl]

I have the following function whcih I want to use to check the number of cells with a font of a particular colour (ie colour index43).

I have a question: How do I make the cell I select at ***1 the active cell.

Does anyone know of an easier way to count the number of cells in a column or row with a specific text colour?

Many Thanks
Ross



Function VerticalCount(Column As String, RowStart As Integer, RowEnd As Integer)

Dim a As String
Dim i As Integer
Dim ColourTextCount As Integer
Dim Col_Red As Double
Dim Col_blue As Double
Dim Col_green As Double

'Initialise Count of Specific Coloured Cells
ColouredCellsCount = 0

CurrentRow = RowStart

Range(Column & RowStart).Select '*******************************************1*

Do While Excel.ActiveCell.Row <= RowEnd
If Excel.ActiveCell.Font.ColorIndex = 43 Then
ColourTextCount = ColourTextCount + 1
Else
End If
CurrentRow = CurrentRow + 1
Range(Column &amp; CurrentRow).Select '***************************************1*
Loop

VerticalCount = ColourTextCount

End Function

 

[dsi]

Since you are already using the Range object, get the color index from it:

If Range("A" & iRow).Font.ColorIndex = 43 Then

Or, if the first empty cell in the column indicates the end of the data, you could loop through the cells this way:

Sub CountCells()
    Dim iRow As Long, iCount As Long
    iRow = 1
    Do While Len(Range("A" & iRow).Text) > 0
        If Range("A" & iRow).Font.ColorIndex = 43 Then
            iCount = iCount + 1
        End If
        iRow = iRow + 1
    Loop
    MsgBox "There are " & iCount & " colored cells."
End Sub