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
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!
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:
- Web design
- Blackmagic design disk speed test windows - Download - Diagnosis and monitoring
- Home design 3d - Download - 3D modeling
- How do i update my facebook account to new version and design - Guide
- Design poster software - Download - Image composition
- Cricut design space download - Download - Other
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
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)
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
May 25, 2010 at 04:05 PM
I have been observing you with Excel messages. You work wonders don't you!
Thank you
May 25, 2010 at 04:12 PM