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.
Hi Steve,
I have modified the code and attached is the version that counts instead of sum.
Jamil,
This modified code does not seem to be working. I am getting a #VALUE! error in my target cell that should be displaying the count of cells with colored text in my selected cell range. Would there be any part of the code I would need to change so that it will work with my cell range containing text? It doesn't seem to matter whether I choose General or Text for the cell format, the formula still results in a #VALUE! error.
Hi,
I opened your file.
the reason you get #NAME error is becuase you have not used UDF which i wrote. you have used another UDF which works only for background color and not for conditional formatting.
to make it work, you need to set conditionally format cells and then place the UDF in the VBA module and then it will work.
I can help with troubleshooting the file, however, I am sorry I cannot build the workbook, as i do not have time.
Hello Mr. Mohammad,
Thank you for sharing your expertise about VBA Code using Conditional Formatting. I am not knowledgeable about the VBA Codes and Conditional Formatting Statements. Sir, I have made this worksheet after searching for VBA Codes online and applying them as per my requirements. I make a rather large worksheet (Range A1 : AK3000) every month wherein lot of calculations are there in Column Range J1:AJ3000. I have used colors for fonts in some cells and then used VBA code "SumByColor" (You can see the VBA Code in Module 4) to get the totals in the relevant cells at the end of the sheet. Sir I am attaching .xlsb sample file named "Master Worksheet" for your reference. I need help regarding Conditional Formatting and then using VBA Code for summing up the cells based on font colors.
My reference in the worksheet here is specially for cells U2, V2, W2, X2 and then with reference to these cells for cells AG3, AH3 and AI3. Currently the sum of cell range W2:W17 is done by "SumByColor" VBA Code.
The color of cells W2, W4, W6, W12 and W15 is based upon the value of Tax Percentage in cells X2, X4, X6, X12 and X15 -for which I am using an IF Statement (=IF(V2<=2499.49,"12%","18%"),
The value of X2 changes to 12% if the room tariff is:
- Less than or equal to 2499.49 : 12 %
The value of X2 changes to 18% if the room tariff is:
- Greater than or equal to 2499.5: 18 %
At present I am changing the color of the cells manually which is becoming a very tedious task. I have set the basic font color of all the cells W2, W4, W6, W12 and W15 as “Blue” and then if the value of cell X2 is 12%, I manually set the font color of cell W2 to Red.
The total of cell range W2:W17 gives the result in cell H18 (Total Basic Amount Room Revenue (Base Tariff for 12% GST) for all the cells with Red Color Font and The total of cell range W2:W17 gives the result in cell H21 for all the cells with Blue Color Font (Total Basic Amount Room Revenue (Base Tariff for 18% GST).
Sir, I tried using conditional formatting to do the above as you have explained with examples, but when I use the “SumConditionColorCells” all I am getting is #NAME? or NO COLOR. Sir please tell me how to do it correctly so that I can get the sum of all the cells with Red Color Font in respective cell and also the sum of all the cells with Blue Color Font in the respective cell.
Similarly, the color of cells AG3, AH3 and AI3 is dependent on the value of cells X2 and the color of cells AG4, AH4 and AI4 is dependent on the value of cell X4. Kindly help me with the conditional formatting formula and the respective VBA code for these cells too.
The font color of cells AG3 and AH3 changes to Red, Accent 2, Darker 50% if the value of X2 is 12% and the font color of AG3 and AH3 changes to Red if the value of X2 is 12%.
The color of cells AG3 and AH3 changes to “RGB(217,151,149)” if the value of X2 is 18% and the color of cells AG3 and AH3 changes to Blue Color Font if the value of X2 is 18%.
The sum total of Cell AG2:AG17 goes to output cell “H32” if the value is for 18% Tax (Blue) and goes to output cell”H34” if the value is for 12% Tax (Red)
The sum total of Cell AH2:AH17 goes to output cell “H33” if the value is for 18% Tax (Blue) and goes to output cell”H35” if the value is for 12% Tax (Red)
The sum total of Cell AI2:AI17 goes to output cell “H36” if the value is for 18% Tax (Blue) and goes to output cell”H37” if the value is for 12% Tax (Red)
Sir I hope you have understood my problem and you shall help me by giving me the conditional formatting formulae for the cells and as well as a VBA Code to execute the command. Thanking you in anticipation.
- Thank you very much!
Hi Jo_Nolaj,
I looked at your workbook, the conditional formatting range and the range used in the UDF was not consistent. i changed it now and it works. plz see attached. please note that instead of 4 CF you could set them with OR function all in one. plz see the CF which i set. the only thing is that CF is replicated for each row using format painter.
Hi, Sir Jamil! Could you kindly help me how to make the UDF and formula you shared on counting the number of rows colored thru conditional formatting work? I copied and paste the UDF for this and followed the formula in your attached sample file, but I cannot get it right. The total is always zero. Here's the file I am working. Thank you very much!
This is awesome, thank you so much for your help!
Dear David,
The issue with your file was, that UDF was using single row range as the range input, while the Conditional Formatting was applies from L2 to Z8 . So, i modified the file Conditional Formatting and now it works.
Also inside the CF formula, you used two functions, while this was not cause of error, but to make it simple and faster, i simplified it with a single function of "Columns".
so =COLUMN(L2)-COLUMN($L2)+1>=$AE2 became =COLUMNS($L2:L2)>=$AE2
Please download and see attached workbook.
Dear Jamil,
Your help has been really enlightening. If you are available, could you please help me by taking a look at his file?
I am using the VB code you created, but the formula doesn't seem to recognize the CF color and I keep getting the #VALUE! refference.
Could you let me know how to fix this?
Thank you Jamil- so easy when you know how. Much appreciated.
Hi Noel,
The VALUE error because your conditional formatting was not set using formula.
If you read my earlier post, I have highlighted that UDF only works if the conditional formatting is set by using formula and not the built-in features of CF.
So, in the attached workbook, I have set the Conditional Formatting using formula and then the UDF works.
please note that SUBTOTALs are not counted as duplicate. please see attached file and examine it by yourself.
Hope it helps.
Jamil I have the same issue as Hyosun. I've entered the VBA code but cannot get it to work. No matter what I do it keeps showing the #Value. I'm trying to count duplicate text based values which seems to be causing some problems. Can you point me towards where the error might be occurring and or a resolution.
Thanks
Noel
- You are welcome Steve.
Thanks for the feedback. Jamil,
I have made this adjustment after adding more sheets and all is working just as it should. Thank you so much for all your help on this issue!
- Thanks for the feedback Steve.
I didn’t change anything in the sheets.
Except that I removed one line from the beginning of the code which was
Application.Volatile
That’s it.
If you add more worksheets there should not be any issue. Jamil,
Please disregard my previous message. After I enabled Macros, saved, and closed the document, I opened it back up and it was running the formulas properly. However, I did have to double click the cell and press Enter to refresh the formula so that it would calculate the correct amount. Regardless, the numbers work and they do not change when I do this to other output cells on separate sheets. I will need to add what you did on these 4 sheets to several other sheets to get them to work properly as well. Could you let me know what you changed so that I can make the same edit to the full workbook and its additional sheets?
Thank you!
Hi Jamil,
I am running into an invalid name error with each COUNTConditionColorCells output cell stating that the formulas contain unrecognized text. I did enable macros on the sheet after downloading it. However, I can see from the document preview that the first 2 sheets are calculating correctly for both green and red text cells, while the 3rd and 4th sheets are calculating green text cells properly but not the red text cells (sheet 3 shows 6 while it should be 9, sheet 4 shows 10 while it should be 7).
Thank you.
Hi Steve,
thanks for uploading the sample data.
Can you please test the attached version?
Hi Jamil,
When I refresh using F9, the current sheet will show the correct data in the output cell, however this appears to interfere with the output cells on other sheets in the workbook which are trying to sum the color condition using a range of cells on their respective worksheet. I have attached a sample of the workbook I'm working on to hopefully better illustrate the situation. Perhaps this is an issue with my conditional formatting formulas, however each sheet is calculating the correct number of colored cells on its own, leading me to believe that this is not the case. Could you take a look and suggest what I might do to fix this? If it requires a UDF for each sheet individually, could you help me with a rewrite to make the code sheet specific?
Thank you very much for your time!
- Hi Steve,
I am not sure if I understood your question.
The UDF calculation method works exactly as the whole workbook calculation like F9 key.
meaning if the calculation is triggered, it will effect all sheets, and not only a specific sheet.
so, if you want the UDF to only calculate on specific sheet, then the code needs to be modified to work on the worksheet level. Hi Jamil,
I have been able to get the UDF to function successfully. However, when I refresh the data on a different sheet to update that sheet's count of colored cells, the sums change on the previous sheet and/or any other sheet which has summed the cells with conditional formatting. The cells within the sheet that I refresh on sum correctly, so my conditional formatting should be accurate. Do I need to establish the UDF to each sheet in the workbook specifically, or is there another way to ensure that the sums calculated on one sheet do not change when a refresh has been run on another sheet? Would there be anything else I would need to check as to why this is happening?
Thank you for your help!
Jamil,
I am not sure why this would not be working either, as the conditional formatting is being determined by formulas. Perhaps this is not working due to an add-in (or lack thereof), or perhaps it has to do with the cells with conditional formatting having drop-down lists? I have attached a draft of my workbook for your reference. Would you be able to take a look and try to see why the formula based on your UDF won't calculate?
Thanks for your help!
Hi Steve,
I have attached the workbook that shows the count of conditional formatted area that has text values in them. it works. I have no idea why it does not work in your workbook. Perhaps, your workbook conditional format rules are not set by formulas. Please read the limitation of the UDF in my post
Alternatively, If it does not work then I suggest that rather than Counting or Summing based on the outcome of the conditional formatting, you use the same criteria as exists in the conditional formatting to Count or Sum the cells using SUMIFS/COUNTIFS or SUMPRODUCT
