Oct 12 2017 02:29 PM
Oct 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/ )
Jun 07 2020 07:24 AM
Hi Jamil,
Kindly you code does not work in case the conditional format color in Pivot table. Please advice.
Jun 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.
Jun 25 2020 01:52 AM
@Jamil Mohammad
Hi Jamil,
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.
much appreicated.
Thanks you
Jun 25 2020 08:29 AM
Thanks.
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.
Jun 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.
Jun 25 2020 10:07 AM
Hi again,
I have made some changes to the formula in the attached file as per your comments in the workbook.
Jun 30 2020 06:43 PM
Jul 02 2020 12:21 AM
Nov 05 2020 04:13 AM
@Jamil Mohammad Thank you for all the help in this thread. It has been of great help.
However, I cannot seem to figure out why the code does not work for my workbook 100% of the time. It seems to skip cells.
I am using CF to give a cell the color greed/red if the date is earlier/later then proposed. I then want to count the amount of red/green cells in a specific column.
In the attachment I have placed an example. The formula is placed in W8 and is looking at column J.
Nov 09 2020 09:33 PM
Hi! Hopefully you are doin well.
I used below UDF at my workings , But outcome is not coming as expected.
Please need your kind help on this issue.
Function COUNTConditionColorCells(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 COUNTConditionColorCells = "NO-COLOR" Exit Function End If COUNTConditionColorCells = CF2 End Function
Nov 23 2020 08:32 AM
Hello @Jamil Mohammad !
I've tried to read your answers and I have managed to install the UDF for counting condition colored cells, but at the most I am only managing the formula to say "NO-COLOR" (sum is not relevant but I've installed it too). I have looked in your example files and the video tutorial you made, but I am still not managing to get it to work, macros should be enabled. I need the sheet to count the amount of red/green cells both horizontally and vertically. The cells are formatted so that when a number within range is added to the cell it turns green, but if a number out of range is added or if the cell is empty it goes red - that part is already done (ranges are written above the cells in blue). Now I just need the color counting in order to calculate what passed and what failed. I have attached a copy of my excel document, and I would be really grateful if you had the time to look through it and check what's missing.
Kind regards, Madeleine Larsson
(English is not my native language, so I apologize if any words are improperly used)
Nov 24 2020 01:15 AM
I checked your file. the conditional formatting you set is not using a formula that is why it is not working.
Nov 24 2020 01:18 AM
It does not work because your conditional formatting "Applied range" is not the same as the range used in the UDF.
Nov 24 2020 01:27 AM
The conditional formatting you set in that file you shared, were not accurately set. it had overlapping conditions and also the ranges were not set properly. Also the conditional formatting rule for blank cells were not used using formula.
as an example, attached is the example, that shows how the UDF works. I got rid of all CFs and left a simple one to show how it works. plz see attached.
Nov 24 2020 02:03 AM
Nov 24 2020 02:22 AM
Jan 19 2021 12:21 PM
=@COUNTConditionColorCells working on 1st row of table and for table total, but returns incorrect values for subsequent rows. Can you help?
Actual values in cells(U22:U25). Calculated values in cells (B33:B40).
Spreadsheet attached
Jan 19 2021 12:42 PM
the CF range was not consistent with the range used in the UDF. I modified in the CF applied range and it works now. please see attached.
Feb 21 2021 02:37 AM
I have followed the UDF but it still could not work. Can you please help?
Mar 04 2021 01:13 PM
Hello Jamil,
I've been trying to add your UDF to a file that i made to check serial numbers on boxes that were shipped, since the shipping department in the company i work in is a mess when it comes to following a sequence in serial numbers and instead they ship whatever box is closer to them, so i was wondering if you could take a look at it I feel that the formula on CF is the problem since i have a formula for each column of shipped boxes 10 in total instead of just one that would work.
Thank you in advance