{"id":64773,"date":"2007-05-29T23:20:00","date_gmt":"2007-05-29T23:20:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/05\/29\/how-can-i-change-the-background-color-of-spreadsheet-rows-that-have-a-date-in-a-specified-cell\/"},"modified":"2007-05-29T23:20:00","modified_gmt":"2007-05-29T23:20:00","slug":"how-can-i-change-the-background-color-of-spreadsheet-rows-that-have-a-date-in-a-specified-cell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-change-the-background-color-of-spreadsheet-rows-that-have-a-date-in-a-specified-cell\/","title":{"rendered":"How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! In an Excel spreadsheet, how can I change the color of a row if there is a date stored a particular cell in that row?

— GL <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, GL. Some of you might have noticed that there was no Hey, Scripting Guy!<\/I> column yesterday. Is that because the Scripting Guys are slacking off and shirking their responsibilities? Of course; after all, why would yesterday be different than any other day? However, the main reason there was no column yesterday is because yesterday was Memorial Day here in the U.S., and all Microsoft employees got the day off. (Yes, even those who don\u2019t really deserve<\/I> a day off.) Couldn\u2019t one of the Scripting Guys have come in and published the column anyway? Sure, but at Microsoft we get very few days off, so we all decided to stay home while we had the chance.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. In case you\u2019re wondering, the official holidays we get are pretty much the ones you would expect a company like Microsoft to observe: Christmas, Thanksgiving, Arbor Day, and Darth Vader\u2019s Birthday.<\/P>\n

No, we\u2019re just kidding; we don\u2019t really get Arbor Day off.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

As we noted, at Microsoft we don\u2019t get very many days off, which means that we don\u2019t really need to keep track of which days are work days and which days aren\u2019t. (Well, most of us don\u2019t; there\u2019s at least one Scripting Guy, who shall remain nameless, who once came in to work on a holiday, and actually worked for about 4 hours before realizing that he was the only one in the building.) But if you (like GL) have spreadsheets that include dates, then you might be interested in a script that can highlight any rows where those dates occur. You know, a script like this one:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
i = 1<\/p>\n
Do Until objExcel.Cells(i, 1) = “”\n    strValue = objExcel.Cells(i, 1)<\/p>\n
    If IsDate(strValue) Then\n        objExcel.Cells(i, 1).EntireRow.Interior.ColorIndex = 44\n    End If<\/p>\n
    i = i + 1\nLoop\n<\/PRE>\n
So how does this script work? Well, to begin with, we\u2019re assuming we have a spreadsheet similar to this:<\/P>\"Microsoft \n

As you can see, this is a pretty simple little file. We have data in some of the cells in column A: a few dates, a number, a text string. Our task is to programmatically pick out the rows that have dates in column A, and then change the background color of those rows. Sound complicated? Trust us, it\u2019s not.<\/P>\n
We start out, as we so often do, by creating an instance of the Excel.Application<\/B> object and setting the Visible<\/B> property to True; that gives us a running instance of Excel that we can view onscreen. We then use these two lines of code to open the spreadsheet C:\\Scripts\\Test.xls and bind us to the first worksheet in that file:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
Once that\u2019s done we assign the value 1 to a counter variable named i; we\u2019ll use this counter variable to keep track of our position in the spreadsheet. (In particular, we\u2019ll use this variable to keep track of which row we happen to be on.)<\/P>\n
At this point we\u2019re ready to start changing row colors. We\u2019ve configured our sample spreadsheet to ensure that there are no blank rows in between rows that contain data. Why did we do that? Because that makes it easy to know when we\u2019ve finished checking all the rows that contain data: if column A in a row is empty (\u201c\u201d) that means that we\u2019ve checked everything that there is to check. With that in mind, we set up a Do Until loop that runs until we encounter a blank cell in column A: <\/P>
Do Until objExcel.Cells(i, 1) = “”\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. What if we did<\/I> have blank rows in the spreadsheet? In a case like that we can employ the UsedRange<\/B> property to help us determine where our data begins and ends. For a sample script that makes use of UsedRange see this Office Space article<\/B><\/A>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
The first thing we do inside the loop is grab the value of cell i, 1; the first time through the loop, that will be row 1, column 1 (i, of course, being the variable that keeps track of the current row.) We then assign the value of that cell to a variable named strValue:<\/P>
strValue = objExcel.Cells(i, 1)\n<\/PRE>\n
Next, we use VBScript\u2019s IsDate<\/B> function to determine whether or not that value is a date:<\/P>
If IsDate(strValue) Then\n<\/PRE>\n
If IsDate returns False then we aren\u2019t dealing with a date after all; therefore, we simply increment the value of i by 1, then loop around and repeat the process with the next row in the spreadsheet. (And yes, IsDate relies on your Regional and Language settings when determining if a given value is a date. Just something you should keep in mind.)<\/P>\n
OK, but what happens if IsDate comes back True<\/I>? Well, that can mean only one thing: there\u2019s a date value in column A. In turn, that means we need to change the background color of this row, something we do using this line of code:<\/P>
objExcel.Cells(i, 1).EntireRow.Interior.ColorIndex = 44\n<\/PRE>\n
This is kind of an awkward-looking command, but it\u2019s ultimately quite simple. What we\u2019re doing here is selecting the current cell (cell i, 1), then extending the selection to encompass the entire row; that\u2019s what the EntireRow<\/B> property does. We then change the background color by setting the Interior.ColorIndex<\/B> property to 44. When all is said and done, and when we\u2019ve finished going through the entire file, that\u2019s going to result in a spreadsheet that looks like this:<\/P>\"Microsoft \n

See how that works? If there\u2019s a date in cell A then that row is going to be colored a \u2026 lovely \u2026 orangish-yellowish-brownish kind of color.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. No, you aren\u2019t stuck with orangish-yellowish-brownish as your only background color. Take a peek at this Office Space article<\/B><\/A> for a look at the different colors available to you, as well as their corresponding numeric values.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
And there you have it, GL. Incidentally, if you\u2019re trying to make holiday plans we have to confess that we don\u2019t actually know the date of Darth Vader\u2019s birthday; all we know for sure is that he was born in the year 41.9 BBY<\/B><\/A>. And yes, that is<\/I> significant; after all, the year 42 BBY is known as Year 7 before the Great Resynchronization and Year 958 after the Ruusan Reformation. The year 42 BBY is also the year Gilead Pellaeon entered the Raithall Academy and the same year that Omo Bouri died. (Bouri, of course, is best known for negotiating the Treaty of Trammis in 124 BBY.)<\/P>\n
And no, we don\u2019t have the slightest idea what any of that means. Fortunately, though, not having any idea what we\u2019re talking about has never been, and never will be, a deterrent for Hey, Scripting Guy!<\/I><\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! In an Excel spreadsheet, how can I change the color of a row if there is a date stored a particular cell in that row?— GL Hey, GL. Some of you might have noticed that there was no Hey, Scripting Guy! column yesterday. Is that because the Scripting Guys are slacking off […]<\/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,711,48,49,3,5],"class_list":["post-64773","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! In an Excel spreadsheet, how can I change the color of a row if there is a date stored a particular cell in that row?— GL Hey, GL. Some of you might have noticed that there was no Hey, Scripting Guy! column yesterday. Is that because the Scripting Guys are slacking off […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64773","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=64773"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64773\/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=64773"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64773"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64773"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}