Office Space article<\/B><\/A> on that very topic.<\/P>\nSo where are we? Turns out we\u2019re right here, with a spreadsheet that looks like this:<\/P>![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/colorsum.jpg\")
\n
What we want to do now is sum up the cells in the spreadsheet. However, we aren\u2019t going to do that by adding the spreadsheet values; obviously adding A plus B plus C plus etc. wouldn\u2019t make much sense. Instead, we want to determine the background color of the cell, assign that cell a value based on the color, and then add those<\/I> values. Confusing? Don\u2019t worry; we\u2019ll explain it all in detail. And when we\u2019re done with that we\u2019ll talk about our dreams and aspirations.<\/P>\n
Before we start reading the individual cells we have a line of code that assigns the value 1 to a counter variable named i; we\u2019ll use this variable to keep track of our position (row) in the spreadsheet. We then set up a Do loop that runs until it encounters a blank cell in column A (at that point we\u2019re assuming we\u2019ve run out of data):<\/P>
Do Until objExcel.Cells(i, 1).Value = “”\n<\/PRE>\nThe first thing we do inside this loop is determine the background color of the cell and then assign the integer value to a variable named intColor; that\u2019s what we do here:<\/P>
intColor = objExcel.Cells(i, 1).Interior.ColorIndex\n<\/PRE>\nAnd once we know the background color of the cell we can start doing a little math:<\/P>
Select Case intColor\n Case 7 intSum = intSum + 5\n Case 8 intSum = intSum + 10\n Case 9 intSum = intSum + 15\n Case 10 intSum = intSum + 20\nEnd Select\n<\/PRE>\nWhat we\u2019ve done here is set up a Select Case statement based on the value of intColor. In addition, we\u2019ve assigned numeric values to each of the colors used in the spreadsheet. For example, the color 7 (a kind of pinkish-purplish sort of thing) has been assigned the value 5; the color 8 (sort of a cyan \u2026 we think) has been assigned the value 10; etc. Inside our Select Case statement we increment another variable (intSum). How much do we increment this variable by? Well, that depends on the cell color. For example, suppose we have a pinkish-purple cell. In that case intColor will be equal to 7, our script will execute this line of code, and intSum will be incremented by 5:<\/P>
Case 7 intSum = intSum + 5\n<\/PRE>\nAgain, nothing too terribly complicated here. If intColor equals 7 then we assign a new value to the variable intSum: the current value of intSum plus 5. The first time through the loop intSum is equal to 0; because of that, when we encounter that pinkish-purplish cell in row 1, column 1 our equation will be 0 + 5 = 5. As a result, intSum will be equal to 5.<\/P>\n
Once we exit the Select Case statement we increment our counter variable by 1, then loop around and repeat the process with the cell in row 2, column 1. After we\u2019ve looped through all the cells the script echoes back the dramatic conclusion:<\/P>
90\n<\/PRE>\nWell, OK. But trust us, it\u2019s more exciting to see it in person than it is to read about it.<\/P>\n
And that should do it, JC. Like we said, we have several other things to take care of this afternoon, so we\u2019re going to have to cut today\u2019s column short. We apologize that you didn\u2019t get to hear more about the Scripting Son. But don\u2019t worry: as usual, he\u2019ll be playing baseball on into the month of August. No doubt you\u2019ll get to hear plenty<\/I> of stories before the season is done.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I assign a background color (based on a specified value) to a spreadsheet cell and then \u201csum\u201d those cells?— JC Hey, JC. Thanks for the question. And yes, you heard correctly: in his last game the Scripting Son did indeed go 4-for-4, banging out three singles and a double. That […]<\/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":[710,48,49,3,5],"class_list":["post-64983","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I assign a background color (based on a specified value) to a spreadsheet cell and then \u201csum\u201d those cells?— JC Hey, JC. Thanks for the question. And yes, you heard correctly: in his last game the Scripting Son did indeed go 4-for-4, banging out three singles and a double. That […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64983","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=64983"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64983\/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=64983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n