This is FANTASTIC! I've been wanting to do this for years! I know you use #38 here, and having watched other videos of yours, you say that other numbers do other things. Where can I find a list of these things please?
Thank you soooooo much Chester! I've been working with a list for some time and was asked for a tally. It just happened to have three different items that I color coded but still had to count. I knew there had to be an easier way. I LOVE EXCEL!!!!
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? My purpose is to Find all records which have 4 or more green cells. Hope it makes sense.
38 is the type_num that returns the cell index. For more on this, you can check out "Excel 4 Macro" functions and learn about all of them and how they work.
Can we make it automatically by vba? I tried by vba function target.calculate, but it doesn't work at all. I used to have meeting, and some cells coloured with green for complete parts to assembly, yellow for partial complete, and red for not complete at all. When I was asked how many red, yellow and green, I use that formula, but if some cells color changed. That function need to be recalculate. Thank you very much for your paying attention
is there a way to get a "True" or "False" value by comparing the colour of 2 different cells? For Example, if Cell A1 and B1 are both filled in with Red then cell C1 should say "Match" or "True"?
I have looked everything in google and haven't found an easy way, and I would like to have one, how Microsoft hasn't thought of this? I have web excel that I would like to count the colors cell as we add and remove names and backgrounds but haven't found a way yet... this was helpful but still cant help me :(
Hy! Awesome video, might apply it in the future, but there is one thing. It looks like it does not work in case of conditional formatting. Is it any way to do it? Should I maybe just use a different attribution number instead of 38? Thanks in advance!
It's not working for me... is it because the cells I'm wanting it to sum have background determined by a conditional formatting? How can I still use this if the cell has a conditional format?
I did the sum CellColour formula & it worked fine. I then linked another spreadsheet to the totals. Now when I open the spreadsheet with the CellColour formulas in it & add more lines to the data, I get a #BLOCKED message coming up when refreshing the cells. Why is this? I have a registered version of Office365 & am connected online. I removed the links & still get the #BLOCKED error message when I try & update new cells.
Why does my Excel program just shut itself off the moment I type in or choose =CellColour like at 1:50? Even if I have more than 2 tabs of Excel open, it just immediately closes the programs.
Chubak Miaouw yes its wonderfull... but i want the figure change when i change the colour, how to do that...don’t want to keep on copy and paste the formula..
Is it possible to change the background color of a shape, based on the value in another cell? For example, let's say I have a cell with a percentage, and I want the background color of a shape to change based on percentage ranges within that cell. So, if the percentage is 30% or less, the background of the shape would turn to red. If it was between 60% and 31%, it would change to yellow. And then if the cell contained a percentage of 61% or above, it would be green. Is it possible to do this without using VBA?
What do you know about the function Cell()? That is supposed to give a number for Cell("color",D4). I haven't figured out yet although Cell("Format",D4) does work, as well as most of the other options "Row", "Col", etc.
The only way you could have the colors updated if you save a color keys on the sheet. For example, list all of the colors you will be using, then copy the color you will need from the list every time instead of using the menu bar.
Suuupeeeerrr!!! I remember that I spent a lot of time looking for a way of how doing this, at last I had to use vba and even if I couldn't get what I expected but this is awesome. One thing, I didn't understand very well why using number 38 in the function?
38 is used to get the color of the cells. The function is used to get various informations of a cell and 38 is to get the color. Other numbers will get other informations. I hope this helps
With Get.Cell you can retrieve data about a cell including background colour. The 38 is the index number for the cell's colour. Other numbers retrieve other info about the cell. Microsoft documentation on this is sparce.
Question for you.. first of all thanks for the trip. Wondering if it is possible to assign a color a value? That is to sat when I highlight a cell that I can associate that color with a value always equaling 10 as an example.. best, Edward
In the conditional formatting.. highlight cells....text that contains...10..... then instead of "light red fill with dark red text" click the drop down choose custom format... press okay.... choose tab fill.... choose the color...choose ok.... then okay
