Forum Discussion
Counting cells based on their colors given from conditional formatting
I have multiple file sheets that contain multiple conditional formatting rules based from formulas. Each row is completely highlighted and I want to count the number of rows that are highlighted in a specific color that's decided by the conditional formatting. I have done some research and have seen things about VBA and macros working, but I haven't been able to get anything to work. Can anyone help me so I can use this across multiple sheets and it can get reran if more data is added or changed.
Thanks!
4 Replies
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 FunctionThis 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
Deleted
Hi Jacob,
the following macro counts all weekends in selection. Here the colorindex is 43. See attachment, too.
Sub ReadColor()
Dim rngcell As Range
Dim i As Integer
For Each rngcell In Selection
If rngcell.DisplayFormat.Interior.ColorIndex = 43 Then
i = i + 1
End If
Next rngcell
MsgBox i
End Sub
Best regards
Berndhttp://https:\\www.vba-Tanker.com
