• 509K Members
• 6,325 Online
• 605K Conversations
SOLVED

# 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

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

please read my earlier post for other users with similar issues.

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

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

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

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.

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

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

tanks your answer.. it is my file

# 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

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

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

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

# 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

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

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

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

# 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!

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

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

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

# 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))

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

# 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

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

# 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

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

Related Conversations
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies