Forum Discussion
Counting cells based on their colors given from conditional formatting
I am doing something similar: conditional formatting in colour then trying to count the cells so colored. The formatting apparently works as the right cells get colored.
But The counting has a problem:
While the VBA thing (linked below) works fine on cells manually colored it does not see see the cells colored by the conditionnal method. However, It is not an issue with the VBA thing since even basic functions such as manual filling for instance also behave as if the cells had not already been colored by the conditionnal protocol. For some reason their conditionally attributed format is not recognised by Excel itself.
Both myself and Jacob would be grateful to get some help about overcoming this issue.
(My point for Jacob is that VBA thing may not be at fault, try it on manually colored cells and you will see that it works. It will fail specifically on conditionnally colored cells as explained above)
https://docs.microsoft.com/en-us/office/troubleshoot/office-developer/count-cells-number-with-color-using-vba
Function CountColorCells(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
CountColorCells = "NO-COLOR"
Exit Function
End If
CountColorCells = CF2
End Function
This is the VBA code im using to create the function, but when I run the function in my spreadsheet it doesn't count properly. I'm not sure if its a conditional formatting issue or and issue with the code
Deleted
To follow up on my last post. I realized why the count isn't coming out properly. The formula I have for my cond. formatting takes precedence over another one. So the function is reading cells as if they had the color code for one color, but then when searched for another, it counts them as well. So basically the code I posted before is reading the cell as having two color codes. How can I get the formula to read one single color code, the one that is showing on the document??
Thanks