Hi all,
I have a spreadsheet in which i need to count numbers in a table. I have specified (via Conditional formatting) to highlight cells where the number is higher than the cell below it.
Subsequently i then have a formula to count and add the conditional formatted cells that have highlighted, referencing D104 as colour:
This works in principle, however what it is not doing is adding up highlighted cells in the specified range (a column in this case). It seems to count one cell only and not add multiple highlighted cells together.
For example, G55 contains 1 and G100 contains 15 - it is only displaying 1, not a total of 16.
Here is the code that i am using:
Any help greatly appreciated.
I have a spreadsheet in which i need to count numbers in a table. I have specified (via Conditional formatting) to highlight cells where the number is higher than the cell below it.
Subsequently i then have a formula to count and add the conditional formatted cells that have highlighted, referencing D104 as colour:
Code:
=SumConditionColorCells(G2:G101,D104)This works in principle, however what it is not doing is adding up highlighted cells in the specified range (a column in this case). It seems to count one cell only and not add multiple highlighted cells together.
For example, G55 contains 1 and G100 contains 15 - it is only displaying 1, not a total of 16.
Here is the code that i am using:
Code:
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 FunctionAny help greatly appreciated.
