Web design

Closed
renea - May 25, 2010 at 10:44 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 25, 2010 at 04:12 PM
Hello,
I need an excel formula that will count the number of cells that are highlighted black and contain data.
Example - 5 cells highlighted black, 4 cells with data, 6 cells blank. Formula should return 9 total cells.

Thanks!


Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 25, 2010 at 04:02 PM
1 Press ALT + F11 to start VBE
2 Click on Insert to add a module
3 Paste the code (after instructions)
4 Call the function as

a. To Find count of all cells in range ,F1:F6 with red color
=CountOnColor(255,False,F1:F6)

a. To Find count of all cells in range ,F1:F6 with red color and with data
=CountOnColor(255,true,F1:F6)

C. To Find count of all cells in range ,F1:F6 with red color and no data
=CountOnColor(255,false,F1:F6) - CountOnColor(255,true,F1:F6)


Function CountOnColor(backGroundColorRGB As Long, CountOnlyNonBlank As Boolean, ParamArray OtherArgs()) As Variant
Dim Count As Double
Dim temp As Variant
Dim Cell As Range
    
    Application.Volatile
    
    On Error GoTo 0

    Count = 0
    If (UBound(OtherArgs) < 0) Then
        MsgBox "Not Sufficient parameters Passed"
        CountOnColor = "** ERROR **"
        Exit Function
    End If
    
    For Each items In OtherArgs
        
        temp = 0
        
        Select Case LCase(TypeName(items))
            
            Case Is = "range"
                
                For Each Cell In items
                
                    If Cell.Interior.Color = backGroundColorRGB Then
                        
                        temp = temp + 1
                        If ((CountOnlyNonBlank) And (Cell = "")) Then temp = temp - 1
                    
                    End If
                Next Cell
                
            Case Else
            
                GoTo NON_CELL_ERROR
                
        End Select
        
        Count = Count + temp
            
    Next items

    CountOnColor = Count
    Exit Function
    
NON_CELL_ERROR:
    MsgBox ("Non Cell/Range parameter encountered")
    CountOnColor = "** ERROR **"
    Exit Function
End Function
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,166
May 25, 2010 at 04:05 PM
Hello Rizvisa1
I have been observing you with Excel messages. You work wonders don't you!
Thank you
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 25, 2010 at 04:12 PM
Thank you.Ambucias. We all learn from each others. I have picked up a trick or two looking at other people code too.