Forum Discussion
sum by color when colors are set by conditional formatting
Hi matt nipper,
since you mentioned that you "have done an exhaustive search online" i have come up with a solution for you, although it has two limitations A) it will only work, if your rules of conditional formatting is created using conditional formatting rules with formula aka (use a formula to determine which cells to format) and B) the UDF will only work if sum range is more than one cell another word, it will not sum a single cell, as well as the conditional formatted range is more than one cell.
the example file, you can download it from here. I could not upload it here, as it is a Excel Macro-enabled Workbook that contains the UDF
Function SumConditionColorCells(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 + CFCELL.Value CF3 = CF3 + 1 Next CFCELL Else SumConditionColorCells = "NO-COLOR" Exit Function End If SumConditionColorCells = CF2 End Function
in the attached example file you can see that from A3:G16 cells are formatted using Conditional Formatting.
User Defined Function (UDF) is placed in cells J2 & J3 and cells I2 and I3 are the criteria color used as reference inside the UDF in J2 and J3.
I hope this helps you.
Edit: uploaded file and updated the code to the correct one.
Everything is working great from all your help but I have one issue that keeps popping up. I have multiple columns that have independent CF that are summed by the CF per each column. I have different people assessing the file and updating it with dates. Randomly, or at least I see it as random, is that all the CF is combined across all the columns. All the CF works perfectly fine still but the CountConditionalCells UDF then only uses the first column and applies that summed values across all the columns. I then have to go into each column and reapply unique (but identical) CF. This is quite frustrating and I am sure it is something I am doing incorrectly. Any help would be greatly appreciated, I read through the earlier posts and could not find this failure mode discussed.
you are probably manually adding the conditional formatting on each column which can be time consuming. you can avoid that, by using the pain brush option of Excel.
select the single column that has the CF and then double click on the Excel paint brush and then now click each of the other columns that you want the CF applied. this way the exact CF will apply without the hassle of manual creation of CF.
Thanks for your message and kind words.
I guess when you involve the manual background changing color to blue, then it will have to be the way that you have already adopted. I am sure there are better ways to make it robust, but this is as far as I could go. Thank you for your understanding.
You're 100% correct, adding them manually and they are time consuming!
A few issues - and I added a small sample excel sample of what I am talking about to make it easier to understand my issues.
1. When I remove a row from the sheet (and it is not at the bottom of all the rows) all the CF is combined across the columns and then negates the CountConditionalCells UDF.
Possible solution:
A. Add a "closed" note to the row but the file is SLOOOW as is based on all the CF this will just keep a lot of extra CF cells I don't need to track or care about.
B. Push the "to be deleted" row to the bottom and delete, but this is not robust and many teams are adopting this file and someone will mess that up.
2. When I add a row to the sheet it only has the CF for that specific row and does not automatically roll the CF into that row. Which is then another manual process to update the CF to capture that row even if with a paint brush method.
3. The entire sheet is based on dates that are auto populated based on previous up stream deliverables and standard lead times to complete the tasks. The only way I have found to mark a cell (deliverable) as complete (and to save the date for scheduling and historical purposes) is to have the team member go in and manually change the color to blue which triggers a CF based on that color to change the font white. So I am unable to do a paint brush option to all the columns even one at a time as there are some that have manually changed backgrounds because the deliverable was completed and the date stored for deriving the next step or historical purposes.
Again, you're the man with this stuff and all the help you have given so far has been amazing and I really appreciate it. Hope you and your family are doing well during the COVID-19 Impact and stay safe Jamil.