• 541K Members
• 4,611 Online
• 644K Conversations
SOLVED

Highlighted

# Re: sum by color when colors are set by conditional formatting

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

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

Highlighted

# Re: sum by color when colors are set by conditional formatting

it is difficult to tell what could cause the return of zero. If you upload a dummy sample, I can look.

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

Highlighted

# Re: sum by color when colors are set by conditional formatting

hi jamil

I used your code but it does not work.  my excel is multiple criteria and ranges . is there any solution for my problem?

Highlighted

# Re: sum by color when colors are set by conditional formatting

Hi,

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.

Highlighted

# Re: sum by color when colors are set by conditional formatting

I cannot tell what would be the issue, unless i see a dummy example file.

Highlighted

Highlighted

# Re: sum by color when colors are set by conditional formatting

@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.

TIA

Highlighted

# Re: sum by color when colors are set by conditional formatting

Hi Mike,

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.

# Re: sum by color when colors are set by conditional formatting

hi samaneeh

if you read my earlier posts, you will see that I have mentioned to other users the same thing that "(you conditional formatting applied range) and (range used in the UDF) both should be the same, otherwise it would not work. Try to do that and it will work.
Highlighted

# Re: sum by color when colors are set by conditional formatting

@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?

Highlighted

# Re: sum by color when colors are set by conditional formatting

Hi, I have put some additional codes in the attached workbook that forces recalculation and that should take care of the issue you described.

Highlighted

# Re: sum by color when colors are set by conditional formatting

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
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 tried to remove the auto sort to see if that worked and then the count is not correct.

Thanks,

JW

Highlighted

# Re: sum by color when colors are set by conditional formatting

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

Hope it helps.

Highlighted

# Re: sum by color when colors are set by conditional formatting

Hi Jamil,

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.

Highlighted

# Re: sum by color when colors are set by conditional formatting

Hi,

You need to look at my earlier posts and see how did I provided solution for other users. Sorry, don't have time to build a custom new solution particular for your file.
Highlighted

# Re: sum by color when colors are set by conditional formatting

Hi Jamil,

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?

Thanks!

Highlighted

# Re: sum by color when colors are set by conditional formatting

Thanks, will have another look through. Appreciate your time.

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

Thank you.

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

=SUMPRODUCT(F4:F23,(E4:E23>=1)*(E4:E23<=200))

Highlighted

# Re: sum by color when colors are set by conditional formatting

In the file you uploaded, the input range for UDF was wrong. I have corrected it and it works fine.

attached is the file.

Highlighted

# Re: sum by color when colors are set by conditional formatting

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?

Thanks,

Tommaso

Highlighted

# Re: sum by color when colors are set by conditional formatting

try this one, and if you still get error, then you need to remove other functions you have put the in the module.

Highlighted

# Re: sum by color when colors are set by conditional formatting

I removed all the functions from the module, but still, #value.

I'm a bit lost. Why is that so?

Cheers

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

Highlighted

# Re: sum by color when colors are set by conditional formatting

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?

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

Thanks!!

Highlighted

# Re: sum by color when colors are set by conditional formatting

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.

Highlighted

# Re: sum by color when colors are set by conditional formatting

Hi Johnny,

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.