Counting cell fill colours in excel 2007 using VBA not conditional formatting

What formula would I need to write if I wanted excel to report on the following: The number of red cells in the range H2:H30, and the answer to be returned in cell B36? The cells have changed colour based on a VBA macro and not through conditional formatting. Thanks

Solution

please see the code below that does specifically what you are asking for.

1) It looks at the range H2:H30 (this can be adjusted by changing the starting range from H2 to another cell, and the counter N can be adjusted if you want to add/reduce rows)

2) It outputs the number of red cells at cell B36

3) The code specifically looks for cells with a color of "255", which is the default red color in Excel 2007. If this isn't the red your macro used to fill in the cells, you will have to determine what color code it is. Assuming you recorded the macro to fill the cells a specific shade of red, you can look in your code for the part that fills the cell in with a color and look for the part that says Selection.Interior and find the ".Color = 255" part.

Sub Macro1()

Dim RedCellCount As Integer
RedCellCount = 0

Range("H2").Select
For N = 1 To 29
    If Selection.Interior.Color = 255 Then
        RedCellCount = RedCellCount + 1
    End If
    ActiveCell.Offset(1, 0).Select
Next N

Range("B36").Select
ActiveCell.FormulaR1C1 = RedCellCount

End Sub

Hope this helps!