How to count number of red font cells or rows
 

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

How to count number of red font cells or rows
 

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

How to count number of red font cells or rows
 

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

How to count number of red font cells or rows
 

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

How to count number of red font cells or rows
 

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