Counting Cells based on Formatting

[Pavertheway]

Hi,

I use this code below to count cells based on their colour, which was coloured via conditional formatting:

Sub SumCountByConditionalFormat()    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    Dim sumRes
    Dim cntCells As Long
    Dim indCurCell As Long
 
    cntRes = 0
    sumRes = 0
 
    cntCells = Selection.CountLarge
    indRefColor = ActiveCell.DisplayFormat.Interior.Color
 
    For indCurCell = 1 To (cntCells - 1)
        If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
            cntRes = cntRes + 1
            sumRes = WorksheetFunction.SUM(Selection(indCurCell), sumRes)
        End If
    Next
   MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _
        "Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _
        Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color"
End Sub

This works well, however I was wondering if I could automate the process - now I have to select my cell range (B3:T47), then select an active cell (Y1) for it to work - and tweaking with the cntCells section I just seem to stop it working correctly!

Any help would be greatly appreciated.

Thanks,

Adam

 

[mumps]

Try:

indRefColor = Range("Y1").DisplayFormat.Interior.Color

This is a direct reference to Y1 so you don't have to select it.

 

[Pavertheway]

Hi,

thanks for your reply - I tried that and for some reason it just doesn't calculate properly.
When I run the Macro with manual input, it gives the count at 132 cells being the colour selected (which is correct).

When I run the Macro with the code you suggested, it gives 669 cells.