Thread: can't figure how to count # of cells minus red cells VBA

Hi I have tried to figure out how to count all cells in a column and subtract any cells that contain characters with a red font. I'll attach an example. I need code that will work in VBA so that in the case that the column does not contain any cells with red numbers it won't come up with a debug error.

In excel I normally do this manually with this formula: =count("") I normally highlight all cells except the red ones.
See attachment for example.

Also, the # of rows is never the same. Some files will be 100 rows, some will be 1000, depending on the store's file we get. so the code would probably have to contain something like .lastactiverow or something. (sorry I'm new to VBA and don't know much)

Thanks in advance!
Curbster

Hi Curbster,

The following code will count all cells in column C that don't have a red font color and then display the non-red count in a message box once finished. You could, of course, have the count entered into a cell on the worksheet instead.

Sub countNonRed()
Dim i As Long, cellCount As Long
For i = 1 To Range("C65536").End(xlUp).Row
    If Range("C" & i).Font.ColorIndex <> 3 Then
        cellCount = cellCount + 1
    End If
Next i
MsgBox cellCount
End Sub

Sure, change:

MsgBox cellCount

to

Range("C" & Range("C65536").End(xlUp).Row + 2).Value = cellCount