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.
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.
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.......
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.
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
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.
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
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.
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..
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.
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 :)
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
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?
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.
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 so much, this was great but, I followed your instructions and had issues when adding colored cells in a column that were colored by conditional formatting. If I manually create the color on the cell, everything works fine but when I try it on conditional cells, it's not working. any suggestions?
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
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.
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?
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.
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 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
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
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
The automation doesn't work. "ERROR: Microsoft Visual Basic: Variable uses an Automation type not supported in the Visual Basic." is what keeps popping up when I try to change from general to workbook. Does anyone have a workaround that actually works and makes sense to a VBA newbie? Every site that I have been to that states they have a workaround is filled with jargon that make absolutely no sense to the average person. Thank you!
Thank you for this great function tutorial. I have been trying to implement it on my sheet, but no matter how many times i check the visual basic editor to see if i inputted it correctly, i don't get the Function Arguments dialog box when i press insert function at the end... how can i solve this?
Thank you very much for this. Only problem is I can not get the Selection Change to work. Ill have to keep working on it . I only selected one row to test it.
Hello dear Leila. I also changed the code to count by color and it works perfectly: Function CountByColor(MatchColor As Range, CountRange As Range) Application.Volatile True Dim cell As Range Dim myColor As Long myColor = MatchColor.Cells(1, 1).Interior.Color For Each cell In CountRange If cell.Interior.Color = myColor Then CountByColor = CountByColor + 1 Next End Function but when I add the selectionchange to the sheet I can't do ctrl+C and ctrl+V, when I do it from the clipboard it works. Any instruction ??
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!!
Nice video! thank you! If you wish to sum over a range of colors, this worked for me: Function sumColor(matchColor As Range, sumRange As Range) As Double Dim cell As Range Dim ccolor As Range sumColor = 0 For Each cell In sumRange For Each ccolor In matchColor If cell.Interior.Color = ccolor.Cells(1, 1).Interior.Color Then sumColor = sumColor + cell.Value End If Next ccolor Next cell End Function
Great video but I found a problem. Whenever the line of code "Activesheet.Calculate" or just "Calculate" is added to the module, it seems to disable the ability to 'cut or copy and "insert copied cells" function on the worksheet. Any ideas as to why or how to fix this?
Great tutorial! I Wonder, what should I change to count the colored cells? I tried changing the IF code to count instead of sum, but than I get the Value error
Hi thank you for this amazing info. I have also a question. When i ad 2 colors white and green, and fill in a number in the white one and i change it to the green color it will not count the value. How can I solve that?
Thanks, this is indeed magic. I am looking for a help however as I need to sum total a cells in a column (or a row or a range or a selection) which are not colored and the value is within 1000+/-1. Could you please help
Thanks for the video. One question, how to sum cells by color and also if we have additional condition (for instance 3 brands on the left that we need also to take into account while summing)
What's uo, Leila! Thanks for your incredible class. I usually use color conditional formatting to see values that are more than, less than or equal to my budget. I have tested this tutorial with this conditional formating, but when I use the immediate to find the color of my cells in SumRange it shows me value 16777215. It means that my cells are uncolored. But cells are colored, I just used the color conditional formatting. How can I solve it?
Her function is useless for conditionally formatted cells, which means that her function is entirely useless since I am not about to go format each cell myself based on a value. Basically, you have to cycle through the range and test whether each cell meets some criterion. In other words, you just use the sumifs() function.
@Leila Gharani How do you count cells by conditionally formatted color? There appear to be macro subroutines that can do this but how can it be done as a function?
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
How can count the number of same colored cells in a row? Let's say conditional formatting turns some cell's background to Green in each row. How can we automatically get the SUM of the those Green colored cells in each row?
@@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. Excellent ' easy to understand and learn' presentation. It worked the way you constructed the excel sheet. However, when I had the number colored by conditional formatting, it did not work. The totals were returned as 0. Can you help? Thanks 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!
Hello Leila, This works fantasticly, however, if I have text in a colored cell within the range, I get a #Value error. Is there a way to have text in the range of cells and still just calculate the cells with numerical values?
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"
Hello Ma'am, I really like your videos. This one is one of them. But When I am using this for one of my columns it's not working. meaning no calculation is going on. is it because it's a table. please help me with it. Thank you.
This is amazing! However (I'm not sure if that was obvious but It only worked when I saved it as an "add-in") I managed to do everything except that last part with activesheet update and I can't figure out why it doesn't work. Is there any way to get help with this?
Dear Leila, I would like to thank you for this excelent short course. I would like to aske you a question with one of my problems, I want to sum cells with color, 12 different colours which are autmaically coloured by using conditional formula . There are all in one column from 1to 400. I am trying to use your VBA formula but it does not work, do you thing any other way of making it working. I would appreciate if you could assit me resolving this problem. Thank you in advance.
Hello. I have an Excel sheet which contains number of line items that I have categorised and I have marked all these line items in 4 different colors. If I have to create a summary table to categorise the line items in 4 different colors. How can I do it. Is there any formula which can count the number of colored cells and return the exact number to the specific category and colour.
