it is just not what you teach, it is also how you present the knowledge to in front of us. I admire your way of teaching. I am only at 16% progress of your online course and already feel that finally I am getting the concept and structure of the Vba. thnx for everyrhing.
your way of explanation makes me to get in to excel and learn more and more. Last two years i have been continuously using various ideas from this site. Thanks a lot.
Wow....!! I have been using excel since 1997 but I wasn't aware of these fantastic function. You are just brilliant. I subscribe already.... I Love You...Mam. I am impressed so much. Many Many Thanks for sharing.......
Hi Leila, Your tutorials are great . The script does not seem to work with conditional formatting colors. Do you have any idea how we can fixed that? If you can help us on this , you'll be a life saver. Thank you .
Fabulous tutorial - Thank You. I'm not sure I totally understood the VBA (just bits of it), but I followed your instructions and my spreadsheet works perfectly. So chuffed.
I found out that some of my cells did not place the total into that cell. I investigated it and found that the format of that cell was not as a number and once it was formatted correctly the totals showed properly. Thank you so much for this tutorial.
Dear Leila.. the kind of knowledge and the skills you have to spread that knowledge is simply awesome.. thanks for all your knowledge sharing.. im a big fan of yours.. sincerely..
super helpful this video. I had no idea how to create a custom function... also all these little details getting the active cell and a cell's colour. thanks muchly :)
Fantastic video: I think, assigning color to variable and than checking will have some overhead if multiple cells have this function. I would go with directly adding it under if condition because checking Boolean is faster than 2 step process of assigning than checking it. If cell.interior.color = matchColor.interior.color then.... will be faster. Thank you for your time Leila.
Hello Leila! am Rashid from Pakistan and these days i am practicing vba excel and i enrolled in your vba course on Udemy approx. one and half month ago
This is excellent, but is it possible to count instead of sum? Also, can it be set up to count conditionally formatted color? Any help is appreciated! Thanks!
this is what I did for counting yellow cells. Function CountColor(countRange As Range) As Double Dim cell As Range For Each cell In countRange If cell.Interior.Color = 65535 Then CountColor = CountColor + 1 End If Next cell End Function
Great tutorial. Everything works fine. Having trouble with the formula automatically updating. I set it up following the instructions and everything works. If I save the document excel stops automatically updating the formula if I make any changes. I have to manually force the cell to update. Has anyone else experienced this if know how I fix this problem?
I had the same issue, found this on Google and it worked for me. Just added 2 lines to Leila's code. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A:C")) Is Nothing Then ActiveSheet.Calculate ActiveSheet.EnableCalculation = False ActiveSheet.EnableCalculation = True End If End Sub
it seems to be very useful even when dealing with a data matrix containing data where I want to show sum of values in different colored cells for each row... can't wait to check this out, have to clean my data first
Hello, great video. Can you advise how to modify the code if you want the count of color cells (conditionally formatted - upto 5 CF rules ) only and not the sum of cell contents?
Hi Leila. Thank you for the video. I've done the selection update as per your tutorial. The numbers are still not updated. I've changed your VBA slightly to count instead of sum by the way.
Thanks a lot Leila the video and trick was awesome you are a great teacher. Even though I am Lecturer myself I find your training and videos amazing. Thanks a lot.
Hello Leila I am great fan of yours and learned lot of excel formulas by seeing your vedios now i need a help hope you would help me in our office we have a daily tracker in that we have 12 agents and works in 24/7 shifts we work on incident tickets as soon as the ticket arrives we have enter the ticket number in that sheet and change the color of the cell manually according to the time the ticket arrived for eg. if ticket came in between 8am to 9 am it will be green if it is between 9 am to 10 am then red if it is between 10 am to 11 am then purple so on so instead of changing the color manually i need a formula or a steps so based on current time when the data entered into a cell the color should change please suggest
Can you make a video showing how to get a percentage of a certain color of a cell? For example I have due dates for when certain tools we use need to be inspected they usually are only good for one year so I created a table with a list of tools and their due date then I used conditional formatting to highlight when it’s expired to be in red when it’s less than 30 days to the due date to be in yellow and when it’s more than 30 days from the due date to be in green and I want to show a percentage of how many tools are not expired or how many green cells I have and to be able to show it in a bar or a graph somehow to look more professional. Can you please help with this I have looked for a video on here with no luck. You are the best I’ve seen at this so I hope you could help me out. Thank you in advance.
I had the same issue, found this on Google and it worked for me. Just added 2 lines to Leila's code. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A:C")) Is Nothing Then ActiveSheet.Calculate ActiveSheet.EnableCalculation = False ActiveSheet.EnableCalculation = True End If End Sub
Thank you for this, Leila! Quick question, is there a way to have a dynamic range? My use case is that when I filter the data source of the values, I need the cell totals to add up to only the cells in the visible range. Thank you!
I found this very useful..but at 08:56 you have referred that for any movement in cell, it will calculate automatically. It is not happening in case Color is getting changed, Any idea???
I'd like to know too. It didn't work for me either. Someone on Stackoverflow had the same problem to which someone replied that color changes don't trigger a recalculation. stackoverflow.com/questions/33417031/application-volatile-for-colored-cells
I have a range of cells which are conditional formatted with different colors. This doesnt work for that. While summing up in the "Fx" function, the empty cells are treated as " " instead of 0 and hence the overall sum function fails. How can this be handled, please?
Hi Leila, Thank you for video, which is very helpful. My only problem is formulas are not auto updated even after changing the colors I have used the same code as suggested by you. What could be the reasons
I had the same issue, found this on Google and it worked for me. Just added 2 lines to Leila's code. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A:C")) Is Nothing Then ActiveSheet.Calculate ActiveSheet.EnableCalculation = False ActiveSheet.EnableCalculation = True End If End Sub
Hi Leila, I'm one of your video subscribers, please, I would like to request that you create me an IF or conditional formatting formula to color only one cell that has a total value. Thanks.
I have become a huge fan on how you help us and better ways to organise and visualise our data. Unfortunately while this is great when dealing with smaller ranges of data, excel 's performance is really letting me down when it comes down to dealing a large number of rows and references across multiple sheets. Any tip on how to boost the performance in corporate license environment would really help make the most out your tutorials! Great channel! Keep it up.
Very nice one deat! Beautiful!!! Small note: suppose the result cell has the same background as calculated cells, so then you can use: "If cell.Interior.Color = Activecell.Interior.Color"
@@LeilaGharani so I thought. Thank you as always. God grant you abundant energy to do the beautiful work you do here (online). I must say that I am your loyal student and has in return referred quite a number who feels the same. I have been a teacher and so can tell of a good one. You are one of the best. Thanks once again and God bless.
Hi Leila, I truly appreciate your work, I keep watching your videos as they are extremely informative and I keep learning new techniques in excel. I see this video has some VBA at a medium level. I wanted to learn VBA from a more beginner level and request you to advise me on where to start ..
I have a VBA course that will take you from beginner to advanced. If you are interested you can check it out here: courses.xelplus.com/p/excel-vba-excel-macros
Heres the Code: Function SumColor(MatchColor As Range, sumRange As Range) As Double Dim cell As Range Dim myColor As Long myColor = MatchColor.Cells(1, 1).Interior.Color
For Each cell In sumRange If cell.Interior.Color = myColor Then SumColor = SumColor + cell.Value End If Next cell End Function
Great video and solved me a huge problem. However I had a problem with the aumatically upload. I've tried the activesheet.calculate as mentioned in the video but is not working. Do you know any other method to do so?
Hi Leila.. love that If Not Intersect() Is Nothing construct. Before understanding that concept, I used the overkill method of ActiveSheet.Calculate on the full Worksheet_SelectionChange event.. which made some of my workbooks very slow. Now, they are much speedier and more efficient. Thanks for the great videos, lessons and insights.. always something fun, new and interesting on your channel and web site. Thumbs up!!
Grab the file I used in the video from here 👉 pages.xelplus.com/sum-color-file
it is just not what you teach, it is also how you present the knowledge to in front of us.
I admire your way of teaching.
I am only at 16% progress of your online course and already feel that finally I am getting the concept and structure of the Vba.
thnx for everyrhing.
Thanks for the kind words. That's good progress on VBA! The beginning is the hardest part.
Best vid ever in this subject. I appreciate you for sharing this. Merci Leila :)
Great Video!!! This is what I've been searching for everywhere.
You are making VBA understandable even for me. That's some gift!
I'm happy to hear that :)
your way of explanation makes me to get in to excel and learn more and more. Last two years i have been continuously using various ideas from this site. Thanks a lot.
You're most welcome. Thank you for your ongoing support.
Wow....!! I have been using excel since 1997 but I wasn't aware of these fantastic function.
You are just brilliant.
I subscribe already....
I Love You...Mam. I am impressed so much.
Many Many Thanks for sharing.......
You're very welcome. Glad you found something new.
Hi Leila,
Your tutorials are great . The script does not seem to work with conditional formatting colors. Do you have any idea how we can fixed that? If you can help us on this , you'll be a life saver.
Thank you .
Mam in every video you share miracle tricks, which not got on you tube. Love you mam...
I'm happy to hear that the videos are useful.
Great video Leila !! Thanks for sharing !! Ctr+Shift+A is a very helpful shortcut do display the fuction arguments quickly.....
You're very welcome Victor. Glad you like the video.
Fabulous tutorial - Thank You. I'm not sure I totally understood the VBA (just bits of it), but I followed your instructions and my spreadsheet works perfectly. So chuffed.
Great job!
I have been using sum base on color for sometime but you have added something new. Thanks a million.
You're very welcome Daniel.
As great as usual. Leila you are Queen of Excel.
Thank you so much for this tutorial.
Kind regards
My pleasure. Glad you like the video.
I found out that some of my cells did not place the total into that cell. I investigated it and found that the format of that cell was not as a number and once it was formatted correctly the totals showed properly. Thank you so much for this tutorial.
Thanks you so much. This is what i needed for a company project. Very clear and very useful! Considering following the VBA course now!
Great to hear! I hope you'll like the course.
Flawless and thorough example of something that's been driving me nuts! Thank you so much!
Glad it helped, John!
Dear Leila.. the kind of knowledge and the skills you have to spread that knowledge is simply awesome.. thanks for all your knowledge sharing.. im a big fan of yours.. sincerely..
Once again came with great work, u have made many of us rich & richer knowledge-wise. Just waiting for the next one very soon
I'm happy the tutorial is helpful for you.
super helpful this video. I had no idea how to create a custom function... also all these little details getting the active cell and a cell's colour.
thanks muchly :)
An absolutely new aspect of Excel
Never had an idea for any such possibility in Excel
I am very much impressed
Thank you
You're very welcome. That's the beauty of Excel. A lot of possibilities.
@@@LeilaGharani
How to know where to use Vlookup, Index/Match/Dget?
Pls Help
That's easy: always Index & Match :)
Fantastic video: I think, assigning color to variable and than checking will have some overhead if multiple cells have this function.
I would go with directly adding it under if condition because checking Boolean is faster than 2 step process of assigning than checking it.
If cell.interior.color = matchColor.interior.color then....
will be faster.
Thank you for your time Leila.
Good idea. Thank you!
Hello Leila! am Rashid from Pakistan and these days i am practicing vba excel and i enrolled in your vba course on Udemy approx. one and half month ago
Superb..!!! I feel Excel Team should bring this function permanently in workbook. Thanks for sharing solution..!!
You're very welcome. Glad you like it.
This is excellent, but is it possible to count instead of sum? Also, can it be set up to count conditionally formatted color? Any help is appreciated! Thanks!
this is what I did for counting yellow cells.
Function CountColor(countRange As Range) As Double
Dim cell As Range
For Each cell In countRange
If cell.Interior.Color = 65535 Then
CountColor = CountColor + 1
End If
Next cell
End Function
thanks again Leila for your best tutorials.
You're very welcome.
You are my Excel Guru...🙂 Thanks!
🙂🙂🙂
Hello Leila, I'm a new subscriber!! Thank you for the content you put out! You are super AWESOME!!!!
Great to have you here José. I'm glad you like the content.
Thank you for your efforts it's the best and very helpfull lesson.
Great value, finally I understand fantastic metod, I'm using Excel in spanish and it worked perfectly. Like for sure 👍🏼
Great tutorial. Everything works fine. Having trouble with the formula automatically updating. I set it up following the instructions and everything works. If I save the document excel stops automatically updating the formula if I make any changes. I have to manually force the cell to update. Has anyone else experienced this if know how I fix this problem?
I am have the same problem does any one know how to fix this
I had the same issue, found this on Google and it worked for me. Just added 2 lines to Leila's code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:C")) Is Nothing Then
ActiveSheet.Calculate
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
End If
End Sub
@@mvohra Thank you for the help. It works
@@mvohra OMG, thank you so much
it seems to be very useful even when dealing with a data matrix containing data where I want to show sum of values in different colored cells for each row... can't wait to check this out, have to clean my data first
thank you so much for taking part of your time to help other, may you find reward in this life and the next!!!
you are brilliant
You're very welcome. As long as it's helpful I am happy :)
I didn't even know this was possible despite using Excel for a long time. Many thanks. :)
I'm glad you found something new.
Hello, great video. Can you advise how to modify the code if you want the count of color cells (conditionally formatted - upto 5 CF rules ) only and not the sum of cell contents?
No response to this I see
Huge thanks Leila. Your video tutorial is great and very informative and helpful. Thanks a lot.
You're very welcome Amilcar. Glad you like the video.
@@LeilaGharani Yeah. You've done great work by sharing your expertise. Really appreciate. Keep it up.
Great lessons! Thank you!
Thanks very helpful video.
Great video!!
Hi Leila. Thank you for the video. I've done the selection update as per your tutorial. The numbers are still not updated. I've changed your VBA slightly to count instead of sum by the way.
Thanks a lot Leila the video and trick was awesome you are a great teacher. Even though I am Lecturer myself I find your training and videos amazing. Thanks a lot.
Thanks for the kind words. I'm glad you like the video.
Thank you SO MUCH 🙏🏼
Thank you ,this is just what I was looking for .
Thanks so much for the easy-to-follow, professional video! Works like a dream on my Macbook. :)
You truly are the excel goddess!!!
😘
Thank you Leila. Excellent as allways.
You're very welcome Enrique. Glad you like it.
Thank you. This was amazingly useful. Simple tool to make and edit preliminary Gantt charts.
Awesome video, competent and fast. Thx a lot!
If i could like this twice I would. So brilliant and straightforward! Thank you!
My pleasure. Thanks for the Like!
Really helpful, Thanks
Thanks once again Leila. May the good Lord richly bless
You're very welcome Robert.
Thank you Leila for letting us know about a new thing today.
You're very welcome. Glad it is helpful.
One of the best tutor ever
Aww, thank you!
No one like you, you are great
Thank you for the kind words!
Hello Leila I am great fan of yours and learned lot of excel formulas by seeing your vedios now i need a help hope you would help me in our office we have a daily tracker in that we have 12 agents and works in 24/7 shifts we work on incident tickets as soon as the ticket arrives we have enter the ticket number in that sheet and change the color of the cell manually according to the time the ticket arrived for eg. if ticket came in between 8am to 9 am it will be green if it is between 9 am to 10 am then red if it is between 10 am to 11 am then purple so on so instead of changing the color manually i need a formula or a steps so based on current time when the data entered into a cell the color should change please suggest
This was very helpfully in calculate my everyday Transactions Records working as wakala in Tz. So happy i found this.
Can you make a video showing how to get a percentage of a certain color of a cell? For example I have due dates for when certain tools we use need to be inspected they usually are only good for one year so I created a table with a list of tools and their due date then I used conditional formatting to highlight when it’s expired to be in red when it’s less than 30 days to the due date to be in yellow and when it’s more than 30 days from the due date to be in green and I want to show a percentage of how many tools are not expired or how many green cells I have and to be able to show it in a bar or a graph somehow to look more professional. Can you please help with this I have looked for a video on here with no luck. You are the best I’ve seen at this so I hope you could help me out. Thank you in advance.
Thank you! That was so easy to follow. When I save the excel document it no longer automatically updates the sum if I change a color. What can I do?
I face that problem too, did you know how to fix it?
I had the same issue, found this on Google and it worked for me. Just added 2 lines to Leila's code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:C")) Is Nothing Then
ActiveSheet.Calculate
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
End If
End Sub
Thank you, this is what i have been looking for a long time. It is really helpful. Big Thank
You're very welcome. I'm glad the video is useful.
Excellent, Very useful. Thanks.
Great presentation on a function I thought was not possible, but having trouble thinking where this would be useful.
Maybe at some point it will come in handy. It's always good to know what is possible.
This is extremely helpful! Thanks Leila!
Thank for this Leila!
Thank you for this, Leila!
Quick question, is there a way to have a dynamic range? My use case is that when I filter the data source of the values, I need the cell totals to add up to only the cells in the visible range.
Thank you!
I'm using this function to help me build something in Minecraft and it's fantastic
Excellent as always!
Thank you. Glad you like it.
Good Laxcering, Vary Usfoul., Thank you.
I have checked some of your tutorials ...Amazing!!!
I'm glad you like the tutorials.
This video is amazing! thank you so much!!! I needed this so much to make my budgets better :) thank you!!!!
I was struggling to do this very thing last week. Thank you.
You're very welcome Michael. Glad I could help with that.
I tried this and it's working. Thank you very much. T.T
Glad it helped!
Truly appreciate your work. Thank you very much
You're very welcome. Glad you find the videos helpful.
Super erklärt!
Vielen Dank!
thanks very use full
Thank you Leila, That was amazing and easy to understand. Very helpful
Thanks again for a great presentation as usual
You're very welcome. Glad you like it Nissim.
Came here for Excel and loved the video as I do your other videos. Comments are weird though
Great to have you here.
I love it great Tuto thanks a lot Leila
I found this very useful..but at 08:56 you have referred that for any movement in cell, it will calculate automatically. It is not happening in case Color is getting changed, Any idea???
I'd like to know too. It didn't work for me either. Someone on Stackoverflow had the same problem to which someone replied that color changes don't trigger a recalculation. stackoverflow.com/questions/33417031/application-volatile-for-colored-cells
Very nice.
We need VBA lessons.
I'm glad to hear that.
Very useful, thanks for sharing
I have a range of cells which are conditional formatted with different colors. This doesnt work for that. While summing up in the "Fx" function, the empty cells are treated as " " instead of 0 and hence the overall sum function fails. How can this be handled, please?
Seems very precise and easy, of-course very well explained but had no luck still; getting error in the function.
Thank you very much
Hi Leila, Thank you for video, which is very helpful. My only problem is formulas are not auto updated even after changing the colors I have used the same code as suggested by you. What could be the reasons
I had the same issue, found this on Google and it worked for me. Just added 2 lines to Leila's code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:C")) Is Nothing Then
ActiveSheet.Calculate
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
End If
End Sub
@@mvohra thanks
Hi Leila, I'm one of your video subscribers, please, I would like to request that you create me an IF or conditional formatting formula to color only one cell that has a total value. Thanks.
Wow thank you, idk anything about VBA but I was able to follow along easily. You are amazing
Great tutorial. Thank you Leila. How should I save it, so the formula =SumColor is available on every excel file?
I have become a huge fan on how you help us and better ways to organise and visualise our data. Unfortunately while this is great when dealing with smaller ranges of data, excel 's performance is really letting me down when it comes down to dealing a large number of rows and references across multiple sheets. Any tip on how to boost the performance in corporate license environment would really help make the most out your tutorials! Great channel! Keep it up.
You could check out this infographic: www.xelplus.com/speed-up-your-excel-files/
Very nice one deat! Beautiful!!! Small note: suppose the result cell has the same background as calculated cells, so then you can use: "If cell.Interior.Color = Activecell.Interior.Color"
Thanks Leila. Seems like I got to learn VBA for custom needs.
It can really be helpful Peter.
@@LeilaGharani so I thought. Thank you as always. God grant you abundant energy to do the beautiful work you do here (online). I must say that I am your loyal student and has in return referred quite a number who feels the same. I have been a teacher and so can tell of a good one. You are one of the best. Thanks once again and God bless.
Superb! The Queen of excel really.
I'm glad you like it :)
Hi Leila, I truly appreciate your work, I keep watching your videos as they are extremely informative and I keep learning new techniques in excel. I see this video has some VBA at a medium level. I wanted to learn VBA from a more beginner level and request you to advise me on where to start ..
I have a VBA course that will take you from beginner to advanced. If you are interested you can check it out here: courses.xelplus.com/p/excel-vba-excel-macros
Another great video tip. Thank you.
You're very welcome Dennis. Glad you like the video.
Heres the Code:
Function SumColor(MatchColor As Range, sumRange As Range) As Double
Dim cell As Range
Dim myColor As Long
myColor = MatchColor.Cells(1, 1).Interior.Color
For Each cell In sumRange
If cell.Interior.Color = myColor Then
SumColor = SumColor + cell.Value
End If
Next cell
End Function
Hi Leila,you are the best,am really happy with this macro but can we make it to run faster?
Thank you . You are super
My pleasure Khalid :)
Great video and solved me a huge problem. However I had a problem with the aumatically upload. I've tried the activesheet.calculate as mentioned in the video but is not working. Do you know any other method to do so?
Same problem sir... 😭
You are an expert! 👍
Great stuff Leila 👏
I'm glad you like it Heiko.
Leila thank you! Dream woman right there
Glad you like it :)
Hi Leila.. love that If Not Intersect() Is Nothing construct. Before understanding that concept, I used the overkill method of ActiveSheet.Calculate on the full Worksheet_SelectionChange event.. which made some of my workbooks very slow. Now, they are much speedier and more efficient. Thanks for the great videos, lessons and insights.. always something fun, new and interesting on your channel and web site. Thumbs up!!
Thank you for the kind words and the thumbs up Wayne!