Hi,

I have a sheet with maybe 870 rows, of which some rows have red text.

I want to run a program which counts how many rows are red.

I have made the following program, and it is giving me a count of 10, when
it should be around 60.

What am I doing wrong ??

I appreciate your help.

Sub Macro2()

Let b = 0

For a = 4 To 870
If Cells(a, 11).Font.Color = -16776961 Then Let b = b + 1
Next a

Cells(873, 10) = b

End Sub

Hi

the code below is one way of doing what your after.

Option Explicit
Dim MyCell, MyRng As Range
Dim i As Integer

Sub CountReds()

Set MyRng = Range("A4:A870")

For Each MyCell In MyRng

If MyCell.Font.ColorIndex = 3 Then

i = i + 1

End If

Next MyCell

Cells(873, 10) = i

End Sub

Hope this helps

Steve

There are 2 reasons that may give wrong results
1) You may havve more than one shade of red which would only give a partial
count
2) In some rows only certain columns may contain red cells

try this code. I checks every column and looks for cells that are not
automatic and black.

Sub Macro2()
Const Automatic = 0
Const Black = 1
Let b = 0

For RowCount = 4 To 870
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColumnCount = 1 To LastCol
If Cells(RowCount, ColumnCount).Font.ColorIndex <> Automatic And _
Cells(RowCount, ColumnCount).Font.ColorIndex <> Black Then

If Cells(RowCount, ColumnCount) <> "" Then
Let b = b + 1
Exit For
End If
End If
Next ColumnCount
Next RowCount

Cells(873, 10) = b

End Sub
"RajenRajput1" wrote:

> Hi,
>
> I have a sheet with maybe 870 rows, of which some rows have red text.
>
> I want to run a program which counts how many rows are red.
>
> I have made the following program, and it is giving me a count of 10, when
> it should be around 60.
>
> What am I doing wrong ??
>
> I appreciate your help.
>
> Sub Macro2()
>
> Let b = 0
>
> For a = 4 To 870
> If Cells(a, 11).Font.Color = -16776961 Then Let b = b + 1
> Next a
>
> Cells(873, 10) = b
>
> End Sub

Thank you,

I tried both options, Joel and Incidental.

Joel, sorry, it didn't work.

And Incidental, it gave me a count of 867. That is the same as 870-4
(inclusive).

I have triple checked, and the font color is red and the stated font color
number (index) is correct.

I like the idea of counting the non black cells, it makes sense, but it is
saying that they all are, when they are not.

I still require a little help.

Thanks

"Incidental" wrote:

> Hi
>
> the code below is one way of doing what your after.
>
> Option Explicit
> Dim MyCell, MyRng As Range
> Dim i As Integer
>
> Sub CountReds()
>
> Set MyRng = Range("A4:A870")
>
> For Each MyCell In MyRng
>
> If MyCell.Font.ColorIndex = 3 Then
>
> i = i + 1
>
> End If
>
> Next MyCell
>
> Cells(873, 10) = i
>
> End Sub
>
> Hope this helps
>
> Steve
>

OK !!

I have it working; really appreciate the help.

I amended my original code;

Sub test2()

Let b = 0

For a = 4 To 870
If Cells(a, 11).Font.Color <> 0 Then Let b = b + 1
Next a

Cells(873, 10) = b

End Sub

It counted the number of non automatic colored cells, just as your idea was,
and it gave an answer of 117, which is what I wanted!

Thanks.

If you or anyone can find why the code didn't work, then please let me know.




"RajenRajput1" wrote:

> Thank you,
>
> I tried both options, Joel and Incidental.
>
> Joel, sorry, it didn't work.
>
> And Incidental, it gave me a count of 867. That is the same as 870-4
> (inclusive).
>
> I have triple checked, and the font color is red and the stated font color
> number (index) is correct.
>
> I like the idea of counting the non black cells, it makes sense, but it is
> saying that they all are, when they are not.
>
> I still require a little help.
>
> Thanks
>
> "Incidental" wrote:
>
> > Hi
> >
> > the code below is one way of doing what your after.
> >
> > Option Explicit
> > Dim MyCell, MyRng As Range
> > Dim i As Integer
> >
> > Sub CountReds()
> >
> > Set MyRng = Range("A4:A870")
> >
> > For Each MyCell In MyRng
> >
> > If MyCell.Font.ColorIndex = 3 Then
> >
> > i = i + 1
> >
> > End If
> >
> > Next MyCell
> >
> > Cells(873, 10) = i
> >
> > End Sub
> >
> > Hope this helps
> >
> > Steve
> >