07-28-2019 06:23 AM
Dear Sir, i have been trying to count the number of cells having two separate conditional formatted colours in a leave planer calendar. However after applying the VBA code that have been given by you i am getting the result as only 1 in my target cell. i have atfached the file, hoping for your help
07-29-2019 10:22 AM
The range you used in your UDF and the the range of conditional formatted cells are not matching, that is why it do not work.
please read my earlier post for other users with similar issues.
07-29-2019 10:26 AM
it is difficult to tell what could cause the return of zero. If you upload a dummy sample, I can look.
08-09-2019 07:52 AM
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:
The value of X2 changes to 18% if the room tariff is:
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.
08-15-2019 05:13 AM
I used your code but it does not work. my excel is multiple criteria and ranges . is there any solution for my problem?
thanks in advanse for your help
08-16-2019 01:26 AM
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.
08-16-2019 01:26 AM
I cannot tell what would be the issue, unless i see a dummy example file.
08-22-2019 01:57 PM
@Jamil Mohammad Thank you for keeping this alive for so long. I have attached a recent project to count cells and am running into an issue with the formula not updating until it is either clicked on or it is behind cell updates by 2 inputs. What am I doing wrong? Changing the "daily usage" on the attached sheet should give you the same result I am seeing.
09-05-2019 06:46 AM - edited 09-05-2019 06:52 AM
the UDF works as it should. your conditional formatting in the cell H2 is that if the cell value is smaller than 5 then turn it red and that is how it now count one because the color of that cell is red. and in cell M3 color is yellow and in your range there is no cell with yellow color, so UDF returns zero.
09-05-2019 06:48 AM
09-13-2019 06:04 AM
@Jamil Mohammad thank you for your reply. I understand but the calculation does not change as the data is updated. It requires a "got focus"/double click to get the sheet to update after a change. See the attached example, the field color can be updated but the count is always one update behind. In the attached you will see the cell count for yellow is 0 but should be one. Save the file and reopen or double click the formula field and it updates? This always leaves my count off by 1 change. Any advise?
09-13-2019 07:17 AM
Hi, I have put some additional codes in the attached workbook that forces recalculation and that should take care of the issue you described.
11-03-2019 02:25 PM - edited 11-04-2019 12:03 AM
is there a way to count color by CF when CF is not using a formula.
i have attached a my file. the CF is set by colmun and i need to count the cells by row.
i managed to get it to count with the following code but i have an auto sort macro in place and as soon as it sorts i get a #Value error.
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
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
COUNTConditionColorCells = "NO-COLOR"
COUNTConditionColorCells = CF2
I tried to remove the auto sort to see if that worked and then the count is not correct.
11-04-2019 04:38 AM
you do not need a UDF for this. what you are trying to achieve can be done using built-in Array formula.
here is the formula =SUMPRODUCT(1-(COUNTIFS($B$4:$AY$4,$B7:$AY7)>0),(--($B$4:$AY$4<>"")))
placed this in BA7 and dragged down
Please see your example file attached.
Hope it helps.
11-13-2019 10:55 PM
I am trying to highlight cells with duplicated text that also have an overlap in dates on a project planner. I have tried to count the name of the team involved and the number of coloured cells on the same date and if the return is above 1, then highlight the team cell (Column G) in red to show the duplication.
My trouble is finding the duplication overlap on a number of highlighted dates.
I feel i may be over complicating this though.
I am not sure if the duplication option in the Conditional Formatting can do this.
I have attached a dummy.
11-21-2019 12:12 PM
12-03-2019 02:01 PM - edited 12-03-2019 02:21 PM
Your help with every one on this subject has been a great help! I have read this post and haven't been able to find anything to help me with the problem that I'm running in to. For some reason the totals by color are not correct. In the attached excel sheet; in Column F, I've set up a Conditional Formatting so that if Column E is greater than 1 but less than 200 it color fills to the light blue, for each row and set up different Conditional Formatting. Then I have the formula SumConditionColorCells on F25 for the total. The values of the light blue cells is really $327.60 but the =SumConditionColorCells is totaling it at $437.28. I saw a post on a rounding issue, I used that code and the original code that was posted and still getting the same value.
Any way that you could take a look?
12-04-2019 11:25 PM
Thanks, will have another look through. Appreciate your time.
12-17-2019 12:01 AM - edited 12-20-2019 02:04 AM
Dear @Jamil Mohammad
I'm experiencing issue with sum of CF cells. I have cells that colors in a dinamic way:
If the sum is less than D3, color the cells in green
If the sum is between D3 and D3+D4 color them in yellow
If the sum is between D3+D4 and D3+D4+D5 color them in red
Colouring works fine, but when I try to sum the value per color, only summing green works, the others return in a #VALUE error.
I believe I've done not a proper color formatting.
Can you please help out?
12-19-2019 08:00 AM
Hi, It did not work, because if you read my earlier posts, I mentioned that UDF only works if your CF range and UDF input range are the same. in your file, they were not the same.
you can use built-in function to acheive the same result. see the formula in the attached workbook.
12-19-2019 08:04 AM
In the file you uploaded, the input range for UDF was wrong. I have corrected it and it works fine.
attached is the file.
12-20-2019 02:11 AM
Dear @Jamil Mohammad
I see the correction in the preview, but when I download the file, I still get #VALUE! and the range in the UDF is still the same.
Can you write the proper function for E4?
12-27-2019 03:10 AM
try this one, and if you still get error, then you need to remove other functions you have put the in the module.
01-06-2020 01:09 AM
I removed all the functions from the module, but still, #value.
I'm a bit lost. Why is that so?
01-06-2020 05:56 AM
I checked the file is fine. It works in my machine.
Sometimes the #Value error gets triggered by some other Add-Ins or Personal.XLSB
before you open the file again, disable any add-ins and personal.xlsb file.
try to see if you have any file in this directory C:\Users\YOURUSERNAME\appdata\Roaming\Microsoft\Excel\XLSTART
if there is try to move it temporarily.
also if you have any Add-In Installed, try to disable the add-in.
02-10-2020 12:21 AM
I have tried working with your formula on a dataset, I want to count the formatted cells within one collumn. However the formula keeps returning an error: "#VALUE!" or keeps returning the value 0.
I think I have correctly added the macro. Also I checked the colourvalues, but they seem to be the same..
Do you have any suggestions for solving this issue?
Thanks in advance!
02-10-2020 05:33 AM
I looked at your file. the reason you are getting error, because you did not set the conditional formatting rules using formula. if you read my earlier posts in this same thread you will see that I have mentioned that for the UDF to work, the conditional formatting needs to be set using formula.
for example, I have changed the yellow part of the conditional formatting in your file and the UDF worked. Please see attached file. you need to change the other conditional formatting the way I changed it and they will work.
02-10-2020 01:44 PM
Jamil, you seem to have a really good handle on this, so I was wondering if you could look at my attached file and help me out.
As you can see, column AY displays sum from columns D through J. I've set conditional formatting to in AY depending on what is displayed in column B. Just as a test, I put dummy data in column D (COOP/COG at 1, DMP at 2, LEOP at 3, etc...). The conditional formatting works perfectly in column AY.
I then tried to use your formula "SumConditionColorCells" in column Q, expecting to see it going in order (COOP/COG 1, DMP 2, LEOP 3 etc...). For some reason DMP is displaying 1 instead of 2, down the list, Conduct is showing 3, File Management 8, etc....)
Do you know why it's not working? Thanks in advance for any insight you can provide.
Hello @Jamil Mohammad,
I see you have been working with everyone on this great code for a couple years now - thanks!
I have a spreadsheet that tracks many different changes with many different functional groups. Each cell is a task to complete and I use your code to sum up the open, late, or closed items by each group I manage via the background color.
The cells have these color calls outs when applied to the CF I have prescribed:
1) Grey (when identified as grey using a different code); 2) Yellow (=""); Green (>today()); Red (<= today()); and finally; Blue for complete (manual change in background color with the conditional formatting to change the text to white). If a date is missed the team member presses Alt + Enter and places the next target date in which that can get done.
My issue is that when items are going from Green to Blue the counter now counts the one cell as both Blue and Green. Also, when a team member places another date in the cell after it is late the CF changes it to Red based on character count but the counter counts both it as Red and Green. Not sure what is happening, appreciate any help Jamil. I have attached a small sample set of data that I am vetting out before applying to the much larger tracker.
Not sure if I understood the problem. as you are saying that DMP should show 2 instead of 1. however, there is only one color assigned for DMP and in the list of AY column only one time the DMP color exist. that is why it shows 1.
the reason for "Conduct" it shows 8 because you are using the SUM version of the UDF. so there is a cell with color associated to the "Conduct" which happens to have the value of 8 and therefore UDF correctly returns that value. If you want to count instead of SUM. then attached is the version to count. but since all of the cells in AY has only single color, the count returns 1.
please see attached.
It was easy to spot what was causing the additional count. It is the UDF "IdentifyColor" you are using inside the Conditional Formatting. Therefore the UDF COUNTConditionColorCells counts them, because blue color criteria overlaps with the green criteria. for example. Green color condition is that if Date is greater than today. Hence, there are three dates that are greater than today in the list and hence UDF returns 3. disregarding the fact that there is another overlapping coloring applied by CF I=IdentifyColor(A1)="002060"
the UDF COUNTConditionColorCells works based on the CF when returns True, if there is a overlap, it does not look at the color, it looks at the condition you applied for specific color.
The solution for the overlap of blue is to simply subtract the value of blue from the count of green as shown in the attached workbook.
In regard to the green and red. there is no overlap, so it works. the green and red are not double counted. Please see attached file.