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