10-12-2017 02:29 PM
10-12-2017 02:29 PM
I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command.
Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ )
06-07-2020 07:24 AM
Kindly you code does not work in case the conditional format color in Pivot table. Please advice.
06-08-2020 04:13 AM
Hi, it does work with Conditional formatting on Pivot tables. please see the example attached. If it did not work for you. perhaps, you are using the wrong range or your CF is not set as it should.
06-25-2020 01:52 AM
Your posts are very well written and informative, many thanks for sharing your knowledge.
I am however struggling to apply the same code and logic to work on my sheet.
Please refer to the attached document, within the work sheet please go to the 'Labour forecast' tab and see that within column G (Cell G15 to be precise) I am trying to get it to count the coloured cells by condition formatting in the corresponding row.
I am getting 'Name' error return despite having the work book saved as a macro enabled sheet.
any help would be great.
06-25-2020 08:29 AM
I looked at the file you shared. the reason you are getting NAME error, because you have many UDFs in the workbook, but none of them were the one I shared in this thread. which is this one
Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range) Application.Volatile 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).Font.Color = ColorRng.Font.Color 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
I noticed overlapped conditional formatting rules, as well as inconsistency of ranges used in UDF versus ranges used in the conditional formatting. So, even if you place the above UDF in the workbook. it will not work, because of the aforementioned issue of inconsistent range and overlapping CF.
I thought maybe you are overkilling this by using UDF, whereas the solution you are looking for can be achieved by using built in functions.
I rebuild the CF and placed some formulas in the cells O to QG and formatted those cells as custom format ;;; which shows nothing, while there is a value in it.
then applied a CF that if any of those cells hold value of 1 then show green. also those cells has nested IF formula to return 1 if the column E is not blank and is greater than row 14. then in column G, I have used a COUNTIF formula to count if row of O to QG hold a value of 1.
Please see attached and let me know if it works.
06-25-2020 09:39 AM
Many thanks for the response and taking the time to help me. If only more people were so kind
I agree there were certainly some conflicts within the code and CF rules, however I believe the fundamentals of what I am trying to achieve has been lost and subsequently the problem remains unsolved.
please see attached sheet with some additional comments.
Hopefully it all makes sense, and thank you again.
For your ease please remember it is within Labour forecast tab, and please note that the it has a 'X&Y' axis frozen pane.
06-25-2020 10:07 AM
I have made some changes to the formula in the attached file as per your comments in the workbook.