{"id":55653,"date":"2008-05-01T17:21:00","date_gmt":"2008-05-01T17:21:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/05\/01\/hey-scripting-guy-how-can-i-change-the-value-and-color-of-a-spreadsheet-cell-based-on-the-sum-of-other-cells-in-that-spreadsheet\/"},"modified":"2008-05-01T17:21:00","modified_gmt":"2008-05-01T17:21:00","slug":"hey-scripting-guy-how-can-i-change-the-value-and-color-of-a-spreadsheet-cell-based-on-the-sum-of-other-cells-in-that-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-change-the-value-and-color-of-a-spreadsheet-cell-based-on-the-sum-of-other-cells-in-that-spreadsheet\/","title":{"rendered":"Hey, Scripting Guy! How Can I Change the Value and Color of a Spreadsheet Cell Based on the Sum of Other Cells in That Spreadsheet?"},"content":{"rendered":"

<\/p>\n

\"Hey, <\/p>\n

Hey, Scripting Guy! I\u2019m new to Excel, and I was wondering how to change the color of a cell if specific requirements are met. I have a spreadsheet that has three columns of numbers; I\u2019d like to add the numbers in each row and, if they total 100, set the text of a fourth column to \u201cYes\u201d and color that cell green. If the numbers don\u2019t<\/i> total 100 I\u2019d like to set the text of column 4 to \u201cNo\u201d and color the cell red. Can you help me with that?
— JR<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, JR. And Happy International Workers Day! Although most Americans are too busy working to pay much attention, May 1st <\/sup>is<\/i> International Workers Day, a \u201ccelebration of the social and economic achievements of the international labor movement.\u201d The Scripting Guys are different; we\u2019re never too busy working to miss out on anything<\/i>.<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. In all fairness we should point out that Americans don\u2019t observe International Workers Day; instead we have our own holiday \u2013 Labor Day \u2013 which falls in September and is also designed to honor the \u201cstrength and esprit de corps of the trade and labor organizations.\u201d<\/p>\n

You know, now that you mention it, that is<\/i> highly unusual for the US to do one thing while the rest of the world does another, isn\u2019t it? Interesting.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

At any rate, we hope that everyone out there has a very happy International Workers Day. And what if you don\u2019t celebrate International Workers Day? That\u2019s OK; after all, May 1st<\/sup> is also Law Day in the US, a day in which people are encouraged to \u201creflect on the role of law in the foundation of the country and to recognize its importance for society.\u201d (Oddly enough, Law Day has never really caught on, at least not in comparison to holidays like, say, Christmas.) If that\u2019s not enough, May 1st<\/sup> is the birthday of country singer Tim McGraw, and<\/i> \u2013 according to the Love Foundation<\/b><\/a> \u2013 May 1st<\/sup> is also Global Love Day. <\/p>\n

And no, we really shouldn\u2019t make any comments about Global Love Day. We\u2019ll leave that to the reader\u2019s imagination.<\/p>\n

Needless to say, that\u2019s all pretty exciting, but we\u2019ve saved the best for last. In the past few weeks we\u2019ve gotten a lot of questions on the same general theme: how can I add up values in Microsoft Excel and then change the color of another cell to reflect the sum of those values? We\u2019ve been hanging on to these questions for a few weeks, waiting until May 1st<\/sup>, which also happens to be International Change the Background Color of a Cell in an Excel Spreadsheet Day. And now the big day has finally arrived. Happy International Change the Background Color of a Cell in an Excel Spreadsheet Day everyone:<\/p>\n

Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\n\ni = 1\n\nDo Until objExcel.Cells(i,1).Value = \"\"\n    intTotal = objExcel.Cells(i,1).Value + objExcel.Cells(i,2).Value + objExcel.Cells(i,3).Value\n    If intTotal = 100 Then\n        objExcel.Cells(i,4).Value = \"Yes\"\n        objExcel.Cells(i,4).Interior.ColorIndex = 4\n    Else\n        objExcel.Cells(i,4).Value = \"No\"\n        objExcel.Cells(i,4).Interior.ColorIndex = 3\n    End If\n    i = i + 1\nLoop\n<\/pre>\n
Let\u2019s see if we can figure out how this script works. (Granted, that does<\/i> sound like a lot of work, which isn\u2019t really in the spirit of International Workers Day. On other hand, we\u2019re not sure that the Scripting Guys actually qualify for anything called International Workers<\/i> Day.)<\/p>\n
To begin with, we create an instance of the Excel.Application<\/b> object and then set the Visible<\/b> property to True; that gives us a running instance of Microsoft Excel that we can see onscreen. As soon as we have that instance we use the Workbooks.Open<\/b> method to open the file C:\\Scripts\\Test.xls; that\u2019s what this line of code is for:<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\n<\/pre>\n
Ah, good question: what does<\/i> Test.xls look like? Well, right now it looks a little like this:<\/p>\n
\"Microsoft <\/p>\n
As you can see, there\u2019s nothing very special here: just three columns of numbers. But don\u2019t despair; in no time at all we\u2019re going to give this script a complete makeover.<\/p>\n
Well, OK, a semi-complete makeover.<\/p>\n
After the spreadsheet is open we set the value of a counter variable named i to 1; we\u2019re going to use this counter variable to keep track of our current row in the spreadsheet. We set the value to 1 because the data we want to work with begins in row 1. What if our spreadsheet has a header row, and the data doesn\u2019t actually start until row 2<\/i>? Beats us; wow, that\u2019s a real problem, isn\u2019t it?<\/p>\n
Pardon? Just set the value of i to 2, to make sure that the script starts off in row 2 of the spreadsheet? That\u2019s a great idea; thanks.<\/p>\n
If only we\u2019d thought of that \u2026.<\/p>\n
Our next step is to set up a Do Until loop that will let us loop through all the rows in the spreadsheet; as you can see, this loop is designed to continue until we encounter a blank cell in column A:<\/p>\n
Do Until objExcel.Cells(i,1).Value = \"\"\n<\/pre>\n\n<\/thead>\n\n\n
\n
Note<\/b>. If you haven\u2019t done much scripting with Excel, the notation Cells(i, 1)<\/b> simply refers to cell A1. How do we get A1 out of Cells(i, 1)? Well, because i is equal to 1 this syntax points to cell row 1, column 1; on other words, cell A1. What if i was equal to 2? Beats us, we \u2013 oh, wait: we do<\/i> know the answer to this one. In that case we\u2019d be looking at cell A2: row 2, column 1.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Inside the loop, the first thing we do is add up the values of the three cells in row 1 (cells A1, B1, and C1); that\u2019s what this line of code is for:<\/p>\n
intTotal = objExcel.Cells(i,1).Value + objExcel.Cells(i,2).Value + objExcel.Cells(i,3).Value\n<\/pre>\n
Again, there\u2019s nothing very fancy going on here: we\u2019re simply grabbing the Value<\/b> of each cell, adding those values together and then storing the data in a variable named intTotal. Which is just exactly the way they would have done it during the International Socialist Conference of 1904, the conference that pushed for the eight-hour workday.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Interestingly enough, it was the American Federation of Labor that convinced labor organizations throughout the world to accept May 1st<\/sup> as International Workers Day. As a side note, having won that battle, and having ensured that employers could not force them to work more than eight hours a day or 40 hours a week, the average American now puts in a 46-hour workweek, and over one-third of all US workers work more than 50 hours a week, with most of those extra hours put in by choice.<\/p>\n
And yes, the average would<\/i> be higher; for better or worse, people who put in the number of hours that the Scripting Guys do tend to bring the overall figure down.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
OK, so we\u2019ve added the value of cells A1, B1, and C1; now what? Well, now we check to see if intTotal is equal to 100:<\/p>\n
If intTotal = 100 Then\n<\/pre>\n
If intTotal is<\/i> equal to 100 then we\u2019ve hit the magic number; in turn, we execute these two lines of code:<\/p>\n
objExcel.Cells(i,4).Value = \"Yes\"\nobjExcel.Cells(i,4).Interior.ColorIndex = 4\n<\/pre>\n
In line 1 we\u2019re simply setting the value of cell D1 (row 1, column 4) to Yes<\/i>. In line 2, we\u2019re setting the value of the Interior.ColorIndex<\/b> property to 4. That might not sound<\/i> particularly exciting, but it is: this is the part of the script where we change the background color of the cell. (As it turns out, the background color of a cell is dictated by the ColorIndex of the cell\u2019s Interior object. Which, as far as we know, is a standard that was also decided upon at the International Socialist Conference of 1904.) In this case, we\u2019re setting the background color to 4, which just happens to be the index number for green.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. How did we know<\/i> that setting the ColorIndex to 4 would color the cell green? Hey, when you\u2019re a Scripting Guy you need to have an encyclopedic knowledge of just about everything, be it the origins of International Workers Day or the index numbers for cell colors in Microsoft Excel.<\/p>\n
Oh, and we also took a look at this Office Space article<\/b><\/a>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Now, what if intTotal doesn\u2019t<\/i> add up to 100? No problem; in that case we simply execute these two lines of code instead:<\/p>\n
objExcel.Cells(i,4).Value = \"No\"\nobjExcel.Cells(i,4).Interior.ColorIndex = 3\n<\/pre>\n
In line 1 we\u2019re setting the Value of cell D4 to No<\/i>, and in line 2 we\u2019re setting the background color of the cell to red. That\u2019s really all there is to it.<\/p>\n
From there we increment the value of our counter variable by 1, then pop back to the top of the loop and repeat the process with the next row in the spreadsheet. When all is said and done, our spiffed-up worksheet should look a little something like this:<\/p>\n
\"Microsoft <\/p>\n
Which is just what JR was hoping it would look like.<\/p>\n
That\u2019s all we have time for today; after all, we have work to do. And yes, it does<\/i> seem a little unfair that the Scripting Guys have to work both on International Change the Background Color of a Cell in an Excel Spreadsheet Day and on International Workers Day. But such is life; as Andy McInerney once noted:<\/p>\n\n<\/thead>\n\n\n
\n
\u201c\u2026 the modern working class – the class of \u2018free labor,\u2019 whose exploitation is hidden by the wage system – is only several hundred years old. Although its exploitation is masked, it is no less brutal. Men, women and children are forced to work long hours in miserable conditions just to eke out a bare subsistence.\u201d<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Coincidentally enough, that passage is almost identical to the official job description for the Scripting Guy who writes this column. (To tell you the truth, he\u2019s still not sure what possessed him to even apply<\/i> for the job in the first place, let alone to take it.)<\/p>\n
See you all tomorrow. And don\u2019t forget, tomorrow is May 2nd<\/sup>, the day on which we celebrate the 814th<\/sup> anniversary of King Richard I of England giving Portsmouth its first royal charter. You won\u2019t want to miss that<\/i>!<\/p>\n
<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I\u2019m new to Excel, and I was wondering how to change the color of a cell if specific requirements are met. I have a spreadsheet that has three columns of numbers; I\u2019d like to add the numbers in each row and, if they total 100, set the text of a fourth column […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[711,48,49,3,5],"class_list":["post-55653","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I\u2019m new to Excel, and I was wondering how to change the color of a cell if specific requirements are met. I have a spreadsheet that has three columns of numbers; I\u2019d like to add the numbers in each row and, if they total 100, set the text of a fourth column […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55653","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=55653"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55653\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=55653"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55653"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55653"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}