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
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.
