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