If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course
You are my hero simple VBA is the best! My work love colour coding spreadsheets and this made life so much easier. A note for anyone who isn't summing a continuous range you can put the range e.g. A2,D2,G2 inside it's own parenthesis within the the sumbycolour function and it will work.
Your video was so clear, thank you! I had followed instructions to set up a SumByColor that failed to mention the need to select the tab to apply the module. Now that I've done that, thanks to your video, it works perfectly.
Thank you - very clear and exactly what I was looking for. I wish I'd seen this long ago. You are a very good instructor! Subscribed. I needed the result to two decimal places so used Double instead of Integer and Long. Thanks to those suggestions to others below!
Thanks Sumit! Love that UDF. LAMBDAs are all the rage right now, but VBA can still do things that LAMBDAs can't, such as your sum by color example. Thanks for sharing the code. Thumbs up!!
I love the video and all your help with excel. How do I get this to add up decimals? It keeps rounding everything up or ignoring if its too low to round.
I just found this video and it is AWESOME! I subscribed. Your explanations are clear and concise. Thank you so much for sharing the coding. I have one question: everything is working except when I enter numbers that have decimals, the formula section is rounding it up/down to a whole number. For instance 3.25 is rounded down in the sum cell to 3.00, but I want it to show the actual 3.25. I have tried just about everything to fix this. What am I missing?
This was great! Exactly what I was after. The figures i'm working with include decimals, I was just wondering what I can do to stop the totals from rounding to .00?
I was looking for the same and found a comment earlier has given the solution and it worked perfectly. Wyrade 3 weeks ago Just replace "Dim TotalSum As Long" with "Dim TotalSum As Double", pretty simple.
This is great thanks very much, one question this doesn't seem to work for cells that are coloured by conditional formatting do you have a solution for this.
Hi there. Thanks for helping with this. I have given some of my cells custom colours and am finding this UMF doesn't work on all of them. Do you have any ideas how to fix this please?
Great Video! I noticed when I do SumByColor, it rounds up or down to the nearest whole number. Is there a way to modify the script so it goes two decimal places out?
This is great. I am trying to sum a time based value using this VBA, it currently gives a #VALUE error. Is there an applicable modification I could do to the module? Thanks.
This is a great video thank you. I have just set up the VBA code but unfortunately it doesn't work as the numbers that I need to add up contain decimals (0.25, 0.5, 0.75 and 1 etc) do you know a way of making the formula work with the inclusion of decimals? Currently it only works if I remove the decimal and make it a whole number. Thanks
Great video on how to sum by color cells but I noticed the result generates an integer (decimals are dropped). Any suggestions on how to carry all the decimals in the SumByColor function. Thanks all.
thank you, this VBA code is amazing, i am trying to use the same code to do a 'counta' instead of sum of range of colored cells. Can you advise how to do that please?
Your video was a huge lifesaver!! 🌟🌟 Made my research task much simpler and efficient 🥺 Although I don't understand why VBA wasn't working/able to sum few of different shaded cells and i ultimately had to copy the sums of those columns, would like to know this, if you have any idea. Ps. Saving this video for future use!!
This is truly incredible, thank you! I'm curious as I've put my vba into my document, it's not counting the cells that have conditional formatting coloring, only if it's original to the cell. Any idea how I can update my VBA code to count the cells with conditional formatting? When I just try to pull what the color index for one of those cells with conditional formatting is, it comes back as -4142 regardless of what color it is.
Hi Summit. Thanks for your video. My understanding is that we cannot use Macro enabled files in the 'On line' version of Excel. In other words no "Activex" controls driving macros work. Is this correct?
This is really an ingenious tool. But I have a little problem with the code. When I use this code rounds to whole euros for example When the sum should actually be 62.18 €, the result in the spreadsheet is 63.00 what is the reason can you help me?
Great tip. I was able to use it when copied to this specific file, but not when I copied it to my Personal Workbook. Any trick to doing that? The function does not come up when I try it in a different workbook.
When the function is saved in Personal Macro Workbook, you can use it in any workbook but you need to add Personal.xlsb before the formula name. In this example, you can use =PERSONAL.XLSB!SUMBYCOLOR(A1:A4,A1) The formula name doesn't show up when typing, and it's confusing that we need to qualify the function by adding the PERSONAL.XLSB! prefix.
I love this, and would find it very useful except that the total is rounded to the nearest whole dollar. I work with very precise numbers and I need all my decimal points. Changing the cell format does not fix the problem. The calculation is giving me a round number. How can I fix this?
Nevermind, I fixed my own problem. The answer is to change the data type from "Integer" to Double in the VBA code. Excellent video! Thanks for the wonderful tip.
@@cloud9trauma, @nick kramer Here's my VBA code: Function SumByColor(SumRange As Range, SumColor As Range) Dim SumColorValue As Double Dim TotalSum As Double SumColorValue = SumColor.Interior.ColorIndex Set rCell = SumRange For Each rCell In SumRange If rCell.Interior.ColorIndex = SumColorValue Then TotalSum = TotalSum + rCell.Value End If Next rCell SumByColor = TotalSum End Function If I recall correctly, the only thing I changed was the word "Integer" in the 2nd and 3rd lines to "Double", which gives me back my decimal points. I did find that when changing the data source, or changing data within the source range, I usually have to refresh the cell with the formula by pressing F2 and then enter. (F9 to recalculate does not work for me.) My example in use is: Cell N1, N2, and N3 all contain a SumbyColor formula. Cell N1 is blue, N2 is yellow and N3 is orange. The lookup range is O1:O1000, so the formula reads =SumByColor(O1:O1000,N1). Then, within the source range for this month, Cell O371 is blue. Cells O368, O369, O417, O477, and O591 are orange, and cells O440 to O443 are yellow. The formulas in column N rows 1-3 will then give me the sum of the appropriate colored cells. Each month, I erase the data in the source range, clear the fill colors, and dump a new data query in the same range. Then I color code the appropriate line items, click on the cells where my Sum formulas are located, press F2 (as if to edit), then enter, and voila! Hope that helps. I'm nowhere near the expert Sumit Bansal is, but I'm trying my best 🙂
Even though I posted above in response to cloud9trauma, here's my code that works in my spreadsheet. Function SumByColor(SumRange As Range, SumColor As Range) Dim SumColorValue As Double Dim TotalSum As Double SumColorValue = SumColor.Interior.ColorIndex Set rCell = SumRange For Each rCell In SumRange If rCell.Interior.ColorIndex = SumColorValue Then TotalSum = TotalSum + rCell.Value End If Next rCell SumByColor = TotalSum End Function
If you are facing the VALUE error to fix this click the FX logo next to the formula bar and define the formula from there. Mine did not calculate with comma but it did with doubledot comma.
Need 1 help from u boss... There r multiple no of coloums with random nos.. question is that...hightlight the greather no compare then previous cell..pls help boss note...any random numbers r there in coloum
Thank you! I followed your explanation and it worked! However, as you said it’s necessary to press F9 to force recalculate. Is it possible to also automate the forced recalculation?
I have the exact same question. Also F9 doesn't work for me. I wonder if there is something I need to turn on for that to work. Only thing that works for me is going to the SumByColor cell, hitting F2, then enter. Then it recalculates. F9 doesn't do anything for me.
This is something I've been trying to do for a while and very often use the SUBTOTAL function until I found your VBA code 😊. However, if I change the cell colour F9 doesn't appear to update the cells with the VBA formula. Any ideas?
I also tried and you're right, F9 doesn't work, I remember it used to work in the older versions. In this case, the only way I can figure out now would be to get into the edit mode in the cell (by double clicking on it or selecting and then pressing F2) and then hitting enter
Thank you very much ....My Doubt why it is not working and showing "Compile Error - Variable not defined" when Option Explict is active. when I remove Option Explict it it working fine.
You can use the below code: Function SumByColor(SumRange As Range, SumColor As Range) Dim SumColorValue As Integer Dim TotalSum As Long Dim rcell As Range SumColorValue = SumColor.Interior.ColorIndex Set rcell = SumRange For Each rcell In SumRange If rcell.Interior.ColorIndex = SumColorValue Then TotalSum = TotalSum + rcell.Value End If Next rcell SumByColor = TotalSum End Function
Thanks for your reply. So if we cannot use Macro enabled files in the 'On line' Excel Version ( which by the way I share lot of documents with MY teams in MS "Teams and Sharepoint' what is the solution ? Is Microsoft ever going to give us an alternative to view and use Excel files with it full potential in the on line version ?. Have you got any inside info if the are working on it ? thanks
As far as I know, for Excel Online, the plan is to use Java Script as the way to get automation done. Some experts believe MSFT will do away with VBA macros altogether in the future, but there is no time line or confirmation on this. I am assuming Excel Online will be a lot like Google Sheets (which also uses JS for automation)
Hi I tried this but I get a Comile error Variable not f defined. The Error is on the row Set rCell = SumRange and highlights rCell. Can you help me resolve this Thanks
Hello Allister.. You can use the below code. I have added a line in the code (Dim rcell As Range), so it would work even when you have option explicit in the module Function SumByColor(SumRange As Range, SumColor As Range) Dim SumColorValue As Integer Dim TotalSum As Long Dim rcell As Range SumColorValue = SumColor.Interior.ColorIndex Set rcell = SumRange For Each rcell In SumRange If rcell.Interior.ColorIndex = SumColorValue Then TotalSum = TotalSum + rcell.Value End If Next rcell SumByColor = TotalSum End Function
thnks dude...but how about if those filled color into cell was already done by conditional formatting???i try to code it after conditional formatting (finding the least value and fill the least value with color and leave the greater value as with no color)but it does not recognized the conditional formatting i've done into data i need to manually fill it again before it recognized the cell value.. here is my formula conditional formatting =I18=MIN($I18:$M18) it will fill the cell having the lowest value this was my vba code: Function Sumbycolor(myRange As Range) For Each myCell In myRange If myCell.interior.color Then mySum = mySum + myCell.Value End If Next Sumbycolor = mySum End Function it shows no value or zero (0) thanks in advance
Why does nobody use the built-in InteriorColor function? If you enter =InteriorColor(B2), Excel will provide a numeric value for the color in cell B2. You can then use that value to write any formula. I often use an IF formula to copy only cells of a certain color: =IF(InteriorColor(B2)=InteriorColor(F35),F35,"")
I also created a new VBA script without Function as below: Option Explicit Sub SumColor() Dim Cell As Range Dim RangeList As Range Dim ColorIndex As Integer Dim Sum1 As Long, Sum2 As Long Sum1 = 0 Sum2 = 0 Set RangeList = Range("b2:b15") For Each Cell In RangeList ColorIndex = Cell.Interior.ColorIndex If ColorIndex = 40 Then
Sum1 = Sum1 + Cell.Value ElseIf ColorIndex = 6 Then
Sum2 = Sum2 + Cell.Value
End If Next Cell Range("b16").Value = Sum1 Range("b17").Value = Sum2 Range("d2").Formula = "=b16" Range("d3").Formula = "=b17" End Sub
'Code created by Sumit Bansal from trumpexcel.com Function GetColorCount(CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function
![Lp. Сердце Вселенной #1 НАЧАЛО ПУТЕШЕСТВИЯ [Новый сезон] • Майнкрафт](http://i.ytimg.com/vi/380Q7gLWGvk/mqdefault.jpg)
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
Thank you Sir....
You are my hero simple VBA is the best! My work love colour coding spreadsheets and this made life so much easier. A note for anyone who isn't summing a continuous range you can put the range e.g. A2,D2,G2 inside it's own parenthesis within the the sumbycolour function and it will work.
Your video was so clear, thank you! I had followed instructions to set up a SumByColor that failed to mention the need to select the tab to apply the module. Now that I've done that, thanks to your video, it works perfectly.
I dont usually comment on youtube videos but this deserves a like and comment. Thankyou brother
Exceptional video Sumit. Thank you very much for your very clear explanations. Surprising the GET.CELL function. That's awesome.
Thanks Ivan... Glad you found the video useful :)
Thank you so much!! This saved me at least an hour and I appreciate how direct you were!
Thank you! This works, I used the VBA for the first time thanks to you :)
Another top video by yourself and the Get.Cell was was a surprising function
Get. Cell 🤗🤗🤗 I used this function 7-8 year back
Thank you so much Patel sir.
Love and respect from Pakistan
Very clear and helpful. Thank you so so soooo much Sir. Much Love from Lesotho...♥♥♥♥♥♥
Thank you - very clear and exactly what I was looking for. I wish I'd seen this long ago. You are a very good instructor! Subscribed. I needed the result to two decimal places so used Double instead of Integer and Long. Thanks to those suggestions to others below!
Glad you found the video useful 😊
Thank you! I was having the same issue and your suggestion helped!
This is exactly what I was looking for. Thanks for the tip on changing the function declaration to Double from Integer and Long.
Your SumByColor formula is a life saver! thank you so much for sharing!
Thanks Sumit! Love that UDF. LAMBDAs are all the rage right now, but VBA can still do things that LAMBDAs can't, such as your sum by color example. Thanks for sharing the code. Thumbs up!!
Thanks Wayne... Excel is getting better and better with new formulas, but VBA still adds a lot of value in some scenarios.
@@trumpexcel How did you took the value of 38 in Get.Cell formula.
Thank you so much Sumit, for the superb video as always!!
Thanks Shiffa... Glad you found the video useful :)
Thank You!!! Very easy step by step explanation. Love it!!
I love the video and all your help with excel. How do I get this to add up decimals? It keeps rounding everything up or ignoring if its too low to round.
I have similar issue. I am trying to add currency figures and it ignores the decimals.
This was a great video and very helpful! Got exactly what I needed to get done using your method! Just earned a subscriber.
Thank you so much for sharing and your generosity!
Thank you so much! This made my day! I love excel and what you can do with it!
I just found this video and it is AWESOME! I subscribed. Your explanations are clear and concise. Thank you so much for sharing the coding. I have one question: everything is working except when I enter numbers that have decimals, the formula section is rounding it up/down to a whole number. For instance 3.25 is rounded down in the sum cell to 3.00, but I want it to show the actual 3.25. I have tried just about everything to fix this. What am I missing?
Found the answer below AND now it is perfect!
Great!!! very helpful for my project! Thank you so much.
Wonderful video - thank you Sumit!
Thanks Sumit. That would be highly useful.
Thank you much sir. Have a great day!
it is quite useful, but how about when I have multiple columns of data to select against single color to sum?
Thanks a lot for the sharing!
This was great! Exactly what I was after.
The figures i'm working with include decimals, I was just wondering what I can do to stop the totals from rounding to .00?
I'm having the same issue, it is rounding, and I haven't found why.
I was looking for the same and found a comment earlier has given the solution and it worked perfectly.
Wyrade
3 weeks ago
Just replace "Dim TotalSum As Long" with "Dim TotalSum As Double", pretty simple.
Excellent lesson, thank you so much
Very good indeed!
Many thanks for elevating our skills sir!
This is great thanks very much, one question this doesn't seem to work for cells that are coloured by conditional formatting do you have a solution for this.
Really great, thank you for sharing
Excellent زبردست
Brilliant - thank-you!
You are Very knowledgeable! :) I bet you know an easy way to do a SUMIF formula up until one column has a name then no name
Hi there. Thanks for helping with this. I have given some of my cells custom colours and am finding this UMF doesn't work on all of them. Do you have any ideas how to fix this please?
This is really cool Sumit. Thank you!
Thanks Uma... Glad you found the video useful :)
Nice video 📹 👍 u bring some neat stuff
Hi, the video is great and all, but when Isum it up, it deosn't sum to the dot. It will auto round up. How do I sum it up to the decimal point?
Great Video! I noticed when I do SumByColor, it rounds up or down to the nearest whole number. Is there a way to modify the script so it goes two decimal places out?
Just replace "Dim TotalSum As Long" with "Dim TotalSum As Double", pretty simple.
@@Wyrade Thank you for the note to switch from "Long" to "Double". EZ does it!
I am SO glad I read through these comments!!! :)
Thanks, great lesson
Thanks... Glad you found the video useful :)
Thanks.very helpful 😀
Thankyou very much, could you please help me some more, i need to have the decimals into it too. But for me, it keeps rounding the numbers to a whole.
always great Sumit
Thanks... Glad you found the video useful :)
This is great. I am trying to sum a time based value using this VBA, it currently gives a #VALUE error. Is there an applicable modification I could do to the module? Thanks.
Super Sir!
This is a great video thank you. I have just set up the VBA code but unfortunately it doesn't work as the numbers that I need to add up contain decimals (0.25, 0.5, 0.75 and 1 etc) do you know a way of making the formula work with the inclusion of decimals? Currently it only works if I remove the decimal and make it a whole number. Thanks
Thank you it works in self-coloring cell. But it doesn't works in conditional formatting cell. Do you have any suggestion?
Nice video
Thats so usefull, thanks man!
Great video on how to sum by color cells but I noticed the result generates an integer (decimals are dropped). Any suggestions on how to carry all the decimals in the SumByColor function. Thanks all.
See the solution @delta_magoo709 posted on how to carry the decimals in the result
Very informative. Will you pls make a vedio on array used in Dictionary. If you already have pls share the link. Thanks
Very informative..
The VBA does not work for me. Every time I try to use it, I get a popup in Excel, saying "There is a problem with this formula". What do I do wrong?
How do you add numbers with a decimal and keep the decimal when you add with the colored cell?
I am looking for the same answer. Have you found a way?
thank you, this VBA code is amazing, i am trying to use the same code to do a 'counta' instead of sum of range of colored cells. Can you advise how to do that please?
Your video was a huge lifesaver!! 🌟🌟 Made my research task much simpler and efficient 🥺 Although I don't understand why VBA wasn't working/able to sum few of different shaded cells and i ultimately had to copy the sums of those columns, would like to know this, if you have any idea.
Ps. Saving this video for future use!!
Probably fractions
Just replace "Dim TotalSum As Long" with "Dim TotalSum As Double",
For the 3rd method, may I know why is the formula has no.38 specifically? The one =Get.Cell(38,B2)
This is truly incredible, thank you! I'm curious as I've put my vba into my document, it's not counting the cells that have conditional formatting coloring, only if it's original to the cell. Any idea how I can update my VBA code to count the cells with conditional formatting?
When I just try to pull what the color index for one of those cells with conditional formatting is, it comes back as -4142 regardless of what color it is.
Hi Summit. Thanks for your video. My understanding is that we cannot use Macro enabled files in the 'On line' version of Excel. In other words no "Activex" controls driving macros work. Is this correct?
Yeah, that's correct
Thanks bro!
This is really an ingenious tool. But I have a little problem with the code. When I use this code rounds to whole euros for example When the sum should actually be 62.18 €, the result in the spreadsheet is 63.00 what is the reason can you help me?
How can the function sumbycolor be more dynamic so that if you change the background color it re calculates the new range?
I have the same question.
HI,
I can't seem to find option for filter by column. I'm using sharepoint. Is there any way to do it?
Great tip. I was able to use it when copied to this specific file, but not when I copied it to my Personal Workbook. Any trick to doing that? The function does not come up when I try it in a different workbook.
When the function is saved in Personal Macro Workbook, you can use it in any workbook but you need to add Personal.xlsb before the formula name. In this example, you can use =PERSONAL.XLSB!SUMBYCOLOR(A1:A4,A1)
The formula name doesn't show up when typing, and it's confusing that we need to qualify the function by adding the PERSONAL.XLSB! prefix.
I love this, and would find it very useful except that the total is rounded to the nearest whole dollar. I work with very precise numbers and I need all my decimal points. Changing the cell format does not fix the problem. The calculation is giving me a round number. How can I fix this?
Nevermind, I fixed my own problem. The answer is to change the data type from "Integer" to Double in the VBA code. Excellent video! Thanks for the wonderful tip.
@@LIUBluejeans Hi, I tried changing this in the VBA code but it didnt seem to work. Can you show me how? Thanks
@@LIUBluejeans Did not work for me as well. Could you paste the code here. Maybe I am missing something?
@@cloud9trauma, @nick kramer
Here's my VBA code:
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Double
Dim TotalSum As Double
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function
If I recall correctly, the only thing I changed was the word "Integer" in the 2nd and 3rd lines to "Double", which gives me back my decimal points. I did find that when changing the data source, or changing data within the source range, I usually have to refresh the cell with the formula by pressing F2 and then enter. (F9 to recalculate does not work for me.)
My example in use is:
Cell N1, N2, and N3 all contain a SumbyColor formula. Cell N1 is blue, N2 is yellow and N3 is orange. The lookup range is O1:O1000, so the formula reads =SumByColor(O1:O1000,N1). Then, within the source range for this month, Cell O371 is blue. Cells O368, O369, O417, O477, and O591 are orange, and cells O440 to O443 are yellow. The formulas in column N rows 1-3 will then give me the sum of the appropriate colored cells. Each month, I erase the data in the source range, clear the fill colors, and dump a new data query in the same range. Then I color code the appropriate line items, click on the cells where my Sum formulas are located, press F2 (as if to edit), then enter, and voila!
Hope that helps. I'm nowhere near the expert Sumit Bansal is, but I'm trying my best 🙂
Even though I posted above in response to cloud9trauma, here's my code that works in my spreadsheet.
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Double
Dim TotalSum As Double
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function
How did he took the value of 38 in Get.Cell formula?
If you are facing the VALUE error to fix this click the FX logo next to the formula bar and define the formula from there. Mine did not calculate with comma but it did with doubledot comma.
Thx a lot for your work but I cold not get it to run in MacBook. Any suggestions?
Need 1 help from u boss... There r multiple no of coloums with random nos.. question is that...hightlight the greather no compare then previous cell..pls help boss note...any random numbers r there in coloum
Thank you , useful video. Summation comes as whole number (rounded). How to change the format to Decimals?
I am having this problem too. Did you find a fix? I've tried everything I can think of and Googled several solutions.
Oh wait - saw the solution below!
I'm using the same VBA code but value shows as zero ,
It doesn't sum up by color, can you please suggest.
Great video. When I press F9 the formula doesn’t refresh.
What if I just want to count how many cells of a certain colour there are within a range?
Hello in my excel workbook it works once fine. Then when i go out and open the file again, the function always outs #NAME?
Thank you! I followed your explanation and it worked! However, as you said it’s necessary to press F9 to force recalculate. Is it possible to also automate the forced recalculation?
I have the exact same question. Also F9 doesn't work for me. I wonder if there is something I need to turn on for that to work. Only thing that works for me is going to the SumByColor cell, hitting F2, then enter. Then it recalculates. F9 doesn't do anything for me.
Hi Sumit, I did not understand get.cell function? Could you please explain it again?
why 38 ?
It's a code Get.Cell function uses to identify the color
This is something I've been trying to do for a while and very often use the SUBTOTAL function until I found your VBA code 😊. However, if I change the cell colour F9 doesn't appear to update the cells with the VBA formula. Any ideas?
I also tried and you're right, F9 doesn't work, I remember it used to work in the older versions. In this case, the only way I can figure out now would be to get into the edit mode in the cell (by double clicking on it or selecting and then pressing F2) and then hitting enter
Thank you very much ....My Doubt why it is not working and showing "Compile Error - Variable not defined" when Option Explict is active. when I remove Option Explict it it working fine.
You can use the below code:
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
Dim rcell As Range
SumColorValue = SumColor.Interior.ColorIndex
Set rcell = SumRange
For Each rcell In SumRange
If rcell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rcell.Value
End If
Next rcell
SumByColor = TotalSum
End Function
Hello Sumit, this Name Manager, Cell Color by Sum is not working and throwing up #NA Error.
I need this buit without VBA code. File needs to be accessed from Web Office
Thanks for your reply. So if we cannot use Macro enabled files in the 'On line' Excel Version ( which by the way I share lot of documents with MY teams in MS "Teams and Sharepoint' what is the solution ? Is Microsoft ever going to give us an alternative to view and use Excel files with it full potential in the on line version ?. Have you got any inside info if the are working on it ? thanks
As far as I know, for Excel Online, the plan is to use Java Script as the way to get automation done. Some experts believe MSFT will do away with VBA macros altogether in the future, but there is no time line or confirmation on this. I am assuming Excel Online will be a lot like Google Sheets (which also uses JS for automation)
@@trumpexcel many thanks for your input. Kind Regards.
Martin ( south africa)
Hi I tried this but I get a Comile error Variable not f defined. The Error is on the row Set rCell = SumRange and highlights rCell.
Can you help me resolve this
Thanks
Hello Allister.. You can use the below code. I have added a line in the code (Dim rcell As Range), so it would work even when you have option explicit in the module
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
Dim rcell As Range
SumColorValue = SumColor.Interior.ColorIndex
Set rcell = SumRange
For Each rcell In SumRange
If rcell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rcell.Value
End If
Next rcell
SumByColor = TotalSum
End Function
Not able to do - compile error : expected : end of statment showing.
thnks dude...but how about if those filled color into cell was already done by conditional formatting???i try to code it after conditional formatting (finding the least value and fill the least value with color and leave the greater value as with no color)but it does not recognized the conditional formatting i've done into data i need to manually fill it again before it recognized the cell value..
here is my formula
conditional formatting =I18=MIN($I18:$M18) it will fill the cell having the lowest value
this was my vba code:
Function Sumbycolor(myRange As Range)
For Each myCell In myRange
If myCell.interior.color Then
mySum = mySum + myCell.Value
End If
Next
Sumbycolor = mySum
End Function
it shows no value or zero (0)
thanks in advance
Thanx
Hi; Need some help with a formula; Can you help or direct me to the video that yo may have set up Regards Rav
i tried but i get #NAME? i am new to VBA please help me
Just returning a value of 0. Not calculating. Please help anyone that had the same problem.
Thanks Sumit. I have Microsoft 365 and I can not get function "Get.cell" . Please explain a little abouit it. And the translator does not work
Hi Fernando... I have recorded this video in Microsoft 365, and I could use the Get.Cell function. Are you getting the BLOCKED error by any chance?
@@trumpexcel Hi Sumit, thanks. No kind of error. I have Microsoft 365 in Spanish. Do you know this translation please? My translator does not work
@TrumpExcel Hi Sumit. In addition to the question about Function "get.cell". Please do you know if you can send a image, also?
Why does nobody use the built-in InteriorColor function? If you enter =InteriorColor(B2), Excel will provide a numeric value for the color in cell B2. You can then use that value to write any formula. I often use an IF formula to copy only cells of a certain color: =IF(InteriorColor(B2)=InteriorColor(F35),F35,"")
which version of excel does this work in?
get.cell is not working for me
Dont Work, get error when Use function.
👍
I also created a new VBA script without Function as below:
Option Explicit
Sub SumColor()
Dim Cell As Range
Dim RangeList As Range
Dim ColorIndex As Integer
Dim Sum1 As Long, Sum2 As Long
Sum1 = 0
Sum2 = 0
Set RangeList = Range("b2:b15")
For Each Cell In RangeList
ColorIndex = Cell.Interior.ColorIndex
If ColorIndex = 40 Then
Sum1 = Sum1 + Cell.Value
ElseIf ColorIndex = 6 Then
Sum2 = Sum2 + Cell.Value
End If
Next Cell
Range("b16").Value = Sum1
Range("b17").Value = Sum2
Range("d2").Formula = "=b16"
Range("d3").Formula = "=b17"
End Sub
Pl also share countbycolor vba code.
Thanks
'Code created by Sumit Bansal from trumpexcel.com
Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorValue Then
TotalCount = TotalCount + 1
End If
Next rCell
GetColorCount = TotalCount
End Function
@@trumpexcel How to add help text? It states now (No help avaiable) in Formule Argument Window? Help is highly appreciated!
if in a decimal place 0.5 can't count.
kindly pleased assist
First
thanks alot but it dont add decimals for me