How to use COUNTIF to count colors of cells under conditional formatting?

Question

I am trying to figure out how to structure a countif formula. I have a table of data along with (broadly speaking) two sets of conditional formatting: - for the range of column D through K, the data is a drop-down Yes/No list. If any of those items are marked as "No" and the date in column C is within 30 days of today, the entire row turns red. I couldn't figure out how to do to do this in one big rule, so there's one rule for each column between D through K. - column L automatically does a time stamp when the row is edited. if any of those dates goes more than 30 days old, the row turns grey.

At the top, I would like to have a row count for red cells and grey cells. How would I do this?

Answer 1

Step 1:

There is option to count by colors, but for that you have to install Kutools in you excel Then you can use these functions easily.

Below is the link to download the Kutools Addin and the required formula with sample.

Link: https://www.extendoffice.com/download/kutools-for-excel.html

Formula: =COUNTBYCELLCOLOR($I$5:$I$19,$K6)

Step 2:

If the above option were not suits your requirement, please refer the link below for through VBA.

Link 1: Need to install Kutools.

https://www.extendoffice.com/documents/excel/2651-excel-count-cells-by-color-conditional-formatting.html

Link 2: VBA Code

https://excelribbon.tips.net/T011725_Using_COUNTIF_with_Colors.html

Answer 2

please try the below one for conditional formatting colors through VBA.

Before using the formula you have to identify RGB of conditional formatting color, then u can color manually by entering RGB in blank cell and then function will work.

VBA Code:

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

Below are the following Steps:

For SUM of Conditional Color:

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