How to count and sum "Condtional formatting" cells by color in Excel 2010?

I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command. Also, I tried a code which gives an inst...

Code

Colors

Conditional

count

sum

Jamil

Feb 26, 2018

I came across this post being unanswered. so here is a User Defined Function in VBA

to SUM

use this UDF

Function SumConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + CFCELL.Value
CF3 = CF3 + 1
Next CFCELL
Else
SumConditionColorCells = "NO-COLOR"
Exit Function
End If
SumConditionColorCells = CF2
End Function

if you want to Count instead of SUM then use the below UDF

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function

these solutions were provided to the similar question asked by other Excel users and worked for them.

For more detail here is the link for that answer

Jamil
Bronze Contributor
Apr 12, 2019
try to see if you have already a CF set for that cell with a different formula. then it will work. so you will need to use a workaround. use countblank function instead.
Laurafer
Copper Contributor
Apr 11, 2019
Jamil
Hi there! I tried adding the formula E$11="" and format is blank but then the COUNTConditionColorCells formula returns #Value! Any idea what I'm doing wrong?
hiepkt12
Copper Contributor
Apr 09, 2019
Jamil
I found this code to be used in my file but I want to switch from "Sub" to "Function". That is, I want the result of the yellow box displayed in the column count color yellow.
Help me!!
Thank you very much!!

Sub DisplayFormatCount()
    'Updateby20150305
    Dim Rng As Range
    Dim CountRange As Range
    Dim ColorRange As Range
    Dim xBackColor As Long
    Dim xFontColor As Long
    On Error Resume Next
    xTitleId       = "KutoolsforExcel"
    Set CountRange = Application.Selection
    Set CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type: = 8)
    Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type: = 8)
    Set ColorRange = ColorRange.Range("A1")
    xReturn        = 0
    For Each Rng In CountRange
        qqq           = Rng.Value
        xxx           = Rng.DisplayFormat.Interior.Color
        If Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then
            xBackColor   = xBackColor + 1
        End If
        If Rng.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color Then
            xFontColor = xFontColor + 1
        End If
    Next
    MsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColor
End Sub
COUNT COLOR YELLOW.xlsx242 KB
Jamil
Bronze Contributor
Apr 09, 2019
You are most welcome.

Yes. you can simply add another condition that cell ="" then select "No color" in the fill
Laurafer
Copper Contributor
Apr 01, 2019
Jamil
That worked perfectly! Thank you so very very much! One more question...Is there any way to have the cells that have no data in them remain uncolored? I was playing with the formulas but as I'm sure you've figured out by now I'm not the best at them. Everything else is working beautifully and I am grateful for what I've got but... :)
Jamil
Bronze Contributor
Mar 29, 2019
Laurafer

thanks for your kind words.

I saw your workbook and there are many issues with the CF formulas you set.

Double condition set for a single cell with contradicts each other.

so example in the cell E28 there are two formulas in CF and both of them contradicts each other.

=IF(E28>D28,"true","pass")
=IF(E28<C28,"True","pass")

another issue is that you set IF formulas to return test in CF which is not correct way to set formula.

for example if you want a condition to turn red if certain condition is met then you can simply use equal sign and there is no need for IF function, in fact IF function ruins it.

for example if you want to turn cell E28 to red if its value is greater than D28 then you can simply use =E28>D28 simply as this. CF will return True if condition is met and false if it is not met.

Please see attached your workbook.

I have added two more UDFs so that you can debug and find the problem.

The first UDF return the formula used in the CF for that cell

Function CondFormulaformula(myCell, Optional cond As Long = 1) As String

CondFormulaformula = myCell.FormatConditions(cond).Formula1
End Function

this second formula evaluates the formula used in CF and returns the evaluated value.

Function CondFormula(myCell, Optional cond As Long = 1) As String

CondFormula = Application.Evaluate(myCell.FormatConditions(cond).Formula1)
End Function

you can see in the column H which i highlighted in yellow. the evaluated CF from your formulas and they are not consistent. with the help of two addtional UDFs you can debug your CF formulas and get the correct result.

Hope this helps.

Formatting Test.xlsm34 KB
Laurafer
Copper Contributor
Mar 29, 2019
Jamil
Good morning Jamil! This thread has been SO helpful! I really appreciate it. Of course, since I'm joining I'm having trouble lol.
I added the UDF and made sure that the CF I'm using is based on formulas however I can't seem to get the results to be stable.
This is a fairly simple application and I'm feeling pretty dumb right now as I'm sure whatever it is I'm doing wrong is an easy fix.
Would you mind terribly taking a look? I tried a couple different formulas in the CF but even the straight-forward ones don't always end in the correct result. Sometimes the count is working and sometimes it isn't.
Any help you can give me would be much appreciated. This is making me nuts lol.

Thanks!

Formatting Test.xlsm31 KB
Jamil
Bronze Contributor
Feb 19, 2019
It should work. i suspect there must be an issue with the way CF range is set.
if you upload a dummy sample, i can take a look.
Ron_2019
Copper Contributor
Feb 18, 2019
When I use the formula, it works rows in the column except for the 2nd row of data. It does not count that row. Has anyone hit this issue or found a way around it? It correctly counts all of the other rows so I know it is looking at the condition formatting.

Forum Discussion

How to count and sum "Condtional formatting" cells by color in Excel 2010?

Resources