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.
Jamil, thank you for helping us...
I have six clusters of data, each is 11 columns by 70 rows.
There is Conditional Formatting in each cluster to the effect that if on another cell (say N2) I type a value and that value is found within all the six clusters of data then all the cells containing that manually inputted value will be highlighted (to a specific color that I already set).
Up to that point all is fine.
Now, I need to display MATCH FOUND when all cells in a row (in one cluster of data) have been conditionally formatted and change the font color to all the cells that meet the criteria.
I know it sounds confusing... I'm dizzy myself... but to illustrate the scenario, I attached a file with dummy data.
In the attached file, to illustrate my point, I manually changed the FONT COLOR to all the cells that meet the criteria (all the cells in that row have been conditionally formatted with different colors, obviously), and I also manually added at the bottom of each cluster of data a MATCH FOUND cell since that's what I also need to see as I manually inputted specific numbers (in another area of the sheet, N2 through N7) that I'm looking for.
I tried with the following formulas but to no avail (the VB module is also in place)...
=REPT("MATCH",COUNT(1/MMULT(TRANSPOSE(ROW(P$2:Z$71))^0,N(P$2:Z$71=O2)))=11) array formula
=IF(SUM(--(MMULT(TRANSPOSE(ROW(P$2:Z$71))^0,--(O2=P$2:Z$71))>0))=11,"MATCH","") regular formula
In the examples I have found in this forum, they count how many cells have been conditionally formatted. My scenario is slightly different: I need to be able to SEE when all the cells in row of a cluster of data have been conditional formatted and to display "MATCH FOUND" and change the font color of those cells.
Thank you for helping.
I have already provided you the solution based on your template and you have confirmed that it worked.
link here https://techcommunity.microsoft.com/t5/Excel/sum-by-color-when-colors-are-set-by-conditional-formatting/m-p/288625/highlight/true#M21674
Your latest file have several issues; CF used without use of formula and formulas outside the CF range and many named ranges with broken links that and requires time to debug and analyse, the time which I do not have. As I only contribute to the extend possible and I cannot dive deep and build solutions.
You can post new questions as a new thread on this forum, perhaps other Experts might be able to help you. Or if you need an instant solution, you can hire a Dev for an hour or so, to make this work for you.
Thanks for your understanding.
Hi waran,
I checked your file. you have sent Conditional formatting for the same color (i.e red) on the same range. if you have multiple condition for the single color. then it needs to be put in one single formula, with OR function. the UDF does not work on multiple condition set for the same color with two different CF rule.
Hi Jamil,
I would like to count the number of coloured cells in a set of data regardless of the numbers within the coloured cells. These colours are determined by conditional formatting. The colours include green, amber and red.
I have applied your VBA code to count colours, however it does not count correctly. It only counts the amber coloured cells from the data set.
From the dummy data I have attached, the number of reds should be 2, the number of ambers should be 1 and the number of greens should be 2.
Could you please have a look at the dummy data attached to determine why there is an issue and best appropriate actions.
Thanks in advance,
Waran.
- Glad to hear that you have already figured it out. Thanks for the feedback.
Jamil,
Thanks for replaying.
It's true... on a previous post I said that the code you gave me works and I stand by that.
The request after that was of a different scenario... the formulas need to be applied in a totally different way...
Also, I already deleted all the CF... the file I send earlier belongs to a bigger workbook where all that conditional formatting is used and some how it stayed (I forgot to check and clean) in this file and the CF even linked to a Sheet4... The CF in the file now is only what's needed.
But no worries... I already figure it out...
Please see attached WORKING file... I upload it here for reference in case any one else might need the same solution.
If you inspect the file, you will see that is not the same problem although is has to do with counting with conditional formatting... which is what this thread is about.
Thanks again Jamil.
