{"id":56323,"date":"2008-01-29T22:28:00","date_gmt":"2008-01-29T22:28:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/01\/29\/hey-scripting-guy-how-can-i-change-the-color-of-a-spreadsheet-cell-based-on-a-range-of-values\/"},"modified":"2008-01-29T22:28:00","modified_gmt":"2008-01-29T22:28:00","slug":"hey-scripting-guy-how-can-i-change-the-color-of-a-spreadsheet-cell-based-on-a-range-of-values","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-change-the-color-of-a-spreadsheet-cell-based-on-a-range-of-values\/","title":{"rendered":"Hey, Scripting Guy! How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?"},"content":{"rendered":"

\"Hey,<\/p>\n

Hey, Scripting Guy! I need a script that can read all the values in a spreadsheet and then change the background colors of the cells in that spreadsheet based on a range of values. For example, if the value is less than 20 the cell gets colored one color; if the cell is between 20 and 40 it gets colored another color; and so on. How do I do that?<\/p>\n

— ESW<\/p>\n

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

Hey, ESW. For you weather junkies out there, yes, last night the Seattle area got hit with another mid-winter snow storm, something that must have dumped at least an inch, maybe even an inch and a half<\/i> of snow on the house of the Scripting Guy who writes this column. Could it be the end of the world? Actually, people in this part of the country would have a much better chance of surviving the end of the world than they have of getting through a little bit of snow. (“Snow weenies” is the term often tossed in our direction. We’d take exception to that, but we can’t: there’s snow on the ground, which means we can’t do anything. And that includes taking exception to something.)<\/p>\n

Of course, we know what many of you are thinking: “Oh my gosh, a whole inch and a half of snow! How will you poor Seattleites survive such a nightmare!?!” Well, we can’t speak for everyone in the Puget Sound region. However, we can<\/i> tell you that the Scripting Guy who writes this column plans to survive the nightmare by lounging around the house, eating doughnuts, drinking coffee, and watching a lot of TV.<\/p>\n

Oh, and, uh … working … from home. <\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. Does this mean that the Scripting Guy who writes this column is a snow weenie? Maybe. But, then again, he’s also sitting at home in pair of sweatpants and a sweatshirt while you non-snow weenies are all dressed up and hard at work.<\/p>\n

Think about it.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

To his credit, the Scripting Guy who writes this column originally did<\/i> plan on trying to make it in to work this morning. As he was watching the snow fall, however, he noticed his neighbor attempt to drive up the hill. A moment later, he watched as that same neighbor come backing down the hill. The neighbor tried again; less than a minute later he was backing down into his driveway. After the neighbor’s third unsuccessful try at negotiating the hill the Scripting Guy who writes this column said to himself, “I think I’ll have another doughnut.”<\/p>\n

And then, after that, he decided to work from home today.<\/p>\n

Anyway, right now it’s snowing again (although the sound on the rooftop suggests that there might be little sleet mixed in) and the only thing on TV is some 30-year-old game show being shown on the Game Show Network. That means we might as well take a few minutes to see if we can write a script that can change the background color of a cell based on a range of values.<\/p>\n

Well, how about that? Turns out that we can<\/i> write just such a script:<\/p>\n

Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\n\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(1)\n\nSet objRange = objWorksheet.UsedRange\n\nFor Each objCell in objRange\n    If IsNumeric(objCell.Value) Then\n       \n        If objCell.Value < 20 Then\n            objCell.Interior.ColorIndex = 3\n        ElseIf objCell.Value < 40 Then\n            objCell.Interior.ColorIndex = 6\n        ElseIf objCell.Value < 60 Then\n            objCell.Interior.ColorIndex = 45\n        End If\n    End If\nNext<\/pre>\n
As you can see, this was actually a pretty easy little script to come up with. We start things off by creating an instance of the Excel.Application<\/b> object and set 
the Visible<\/b> property to True; that gives us a running instance of Microsoft Excel that we can see on screen. We then use these two lines of code to open 
the file C:\\Scripts\\Test.xls and bind to the first worksheet in that file:<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(1)<\/pre>\n
At this point, we’re ready to have some real<\/i> fun!<\/p>\n
Oh, right: we have to finish today’s column first, don’t we? OK, let’s finish today’s column first. Then<\/i> we’ll be ready to have some real fun!<\/p>\n
Oh, right: the 2008 Winter Scripting Games<\/b><\/a> don’t start until February 15th<\/sup>. OK, let’s finish today’s column, and then sit around until February 15th<\/sup>. And 
then<\/i> we’ll be ready to have some real fun!<\/p>\n
In the meantime, we use this line of code to create an instance of Excel’s Range<\/b> object, a range that encompasses all the cells in the spreadsheet that contain 
data. (Hence the name UsedRange<\/i>.):<\/p>\n
Set objRange = objWorksheet.UsedRange<\/pre>\n
Why do we want a collection of all the cells in the spreadsheet that contain data? You got it: that makes it very easy to loop through all the cells and, if the 
value of a cell warrants, change the background color.<\/p>\n
To do that, we begin by setting up a For Each loop to loop through all the items in the range (that is, all the cells in the spreadsheet). Inside that loop we use 
the IsNumeric<\/b> function to see if the Value<\/b> of the cell is even a number in the first place. If it is, we then use this block of code to determine the exact value, 
and to see if the value falls in any of our specified ranges:<\/p>\n
If objCell.Value < 20 Then\n    objCell.Interior.ColorIndex = 3\nElseIf objCell.Value < 40 Then\n    objCell.Interior.ColorIndex = 6\nElseIf objCell.Value < 60 Then\n    objCell.Interior.ColorIndex = 45\nEnd If\n<\/pre>\n
As you can see, in line 1 we check to see if the Value of the cell is less than 20. If it is, we then use this line of code to set the background color of the cell to 
red:\n<\/p>\n
objCell.Interior.ColorIndex = 3<\/pre>\n\n<\/thead>\n\n\n
\n
Note<\/b>. How did we know that setting the Interior.ColorIndex<\/b> property to 3 would change the background color of the cell to red? Well, you might say that 
a little birdie told us.<\/p>\n
Well, OK; actually, that little birdie told us to read the Office Space<\/i> article Changing the Background Color of a Cell<\/b><\/a>. Either way, though, the background 
color gets changed to red.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Suppose that the Value of the cell is<\/i> less than 20. In that case, the background color of the cell gets changed to red, and we automatically exit the If Then 
block; as soon as VBScript encounters a true condition it exits an If Then statement. Now, what if the value isn’t<\/i> less than 20? Well, in that case we check 
the next condition: is the Value less than 40? If it is, then we change the background color to yellow (6). If it’s not, then we check our final condition: is the Value 
less than 60? If it is, we change the background color to orange (45); if it’s not, we leave the cell color as-is. Regardless, we then zip back to the top of the 
loop and repeat the process with the next cell in the spreadsheet.<\/p>\n
When all is said and done we should have a spreadsheet that looks like this:<\/p>\n
\"Spacer\"<\/p>\n
Beautiful, yet functional. Just like the Scripting Guys.<\/p>\n
That should do it, ESW. As for the Scripting Guy who writes this column, he has a busy day ahead of him. For example, should he make himself a grilled 
cheese sandwich for lunch, or should he walk over to Dairy Queen and get a hamburger? Boy, working at home is proving to be much harder than he thought 
it would be.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I need a script that can read all the values in a spreadsheet and then change the background colors of the cells in that spreadsheet based on a range of values. For example, if the value is less than 20 the cell gets colored one color; if the cell is between 20 […]<\/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-56323","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! I need a script that can read all the values in a spreadsheet and then change the background colors of the cells in that spreadsheet based on a range of values. For example, if the value is less than 20 the cell gets colored one color; if the cell is between 20 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56323","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=56323"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56323\/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=56323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=56323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=56323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}