{"id":69493,"date":"2005-06-29T18:10:00","date_gmt":"2005-06-29T18:10:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/06\/29\/how-can-i-change-the-background-color-of-a-cell-depending-on-the-date\/"},"modified":"2005-06-29T18:10:00","modified_gmt":"2005-06-29T18:10:00","slug":"how-can-i-change-the-background-color-of-a-cell-depending-on-the-date","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-change-the-background-color-of-a-cell-depending-on-the-date\/","title":{"rendered":"How Can I Change the Background Color of a Cell Depending on the Date?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! I have a Microsoft Excel spreadsheet I periodically analyze using a script. How can I change the color of a cell to red if the date stored in that cell has passed?

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

Hey, DM. We were going to start today\u2019s column off with some clever and witty remark about red-letter dates, but we couldn\u2019t actually think of one. Therefore, we\u2019ll pretend that we don\u2019t have time for such nonsense and instead get right down to business.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. This column is written by the same Scripting Guy who, a short time ago, found himself unable to think of a word that started with a letter K<\/I>. (Yes, yes: kite, kitten, kid, kindergarten; he\u2019s well aware – now – that millions<\/I> of words start with the letter K<\/I>.) At the rate his mind is deteriorating this column will soon consist of a question and then a big blank area where the answer was supposed to go.<\/P>\n

Hey: who said that would be an improvement<\/I>?!?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

In order to keep the explanation focused on the task at hand, let\u2019s assume you have a very simple little spreadsheet, one that consists of a series of dates (all in column A). Our simple little spreadsheet looks something like this:<\/P>\"Microsoft \n


What we want to do is create a script that can open this file, check the date in each cell and, if that particular date has already passed, change the background color of the cell to red. In other words, after the script finishes we want our spreadsheet to look like this:<\/P>\"Microsoft \n


So can we do this? Well, while our Scripting Guy no longer seems capable of using the English language, he\u2019s at least able to write scripts every now and then:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)<\/p>\n
x = 1<\/p>\n
Do Until objExcel.Cells(x,1).Value = “”\n    If CDate(objExcel.Cells(x,1).Value) < Date Then\n        objExcel.Cells(x,1).Interior.ColorIndex = 3\n    End If\n    x = x + 1\nLoop\n<\/PRE>\n
All things considered it is<\/I> a short little script, isn\u2019t it? The script starts out by creating an instance of the Excel.Application<\/B> object and then setting the Visible<\/B> property to True. We then use the Open<\/B> method to open the file C:\\Scripts\\Test.xls. All that simply opens up our spreadsheet and makes it visible on screen.<\/P>\n
After our spreadsheet pops up we assign the value 1 to a counter variable named x; we\u2019ll use this variable to keep track of the current row as we work our way through the spreadsheet. Next we, well, start working our way through the spreadsheet, setting up a loop that continues until we encounter a blank value in column A. That\u2019s what this line of code does:<\/P>
Do Until objExcel.Cells(x,1).Value = “”\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Why do we do that? Well, for the sake of simplicity we\u2019re assuming you have no blanks cells in column A. Therefore as soon as we encounter a blank cell we\u2019ll know that we\u2019ve run out of data.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Inside our Do Loop we check to see if the value of the current cell is less than today\u2019s date. We do that by taking the value of the current cell – objExcel.Cells.(x,1).Value – and using the VBScript CDate<\/B> (character-to-date) function to ensure that the value is treated as a date. We then compare that value to the current date (which we can get by using the Date<\/B>) function. If the cell value is less then the current date, then we use this line of code to change the interior (background) color of the cell to red:<\/P>
objExcel.Cells(x,1).Interior.ColorIndex = 3\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. How did we know that setting the ColorIndex<\/B> to 3 would give us a red background? We didn\u2019t, at least not until we ran the script referenced here<\/B><\/A> that shows us all the background colors and their corresponding values.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Got all that? If you\u2019re not sure why we\u2019re checking the value of objExcel.Cells(x,1) remember that x is a variable we created to keep track of the current row. Because x starts off with the value 1 what we\u2019re doing the first time through the loop is checking cell (1,1): row 1, column 1. After making this first check we increment x by 1; thus the next<\/I> time through the loop we\u2019ll be checking cell (2,1): row 2, column 1. Etc.<\/P>\n
Just that easy, just that quick. We guess you could say that\u2026that\u2026well, OK, we can\u2019t think of anything to say at the moment. But you get the idea.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have a Microsoft Excel spreadsheet I periodically analyze using a script. How can I change the color of a cell to red if the date stored in that cell has passed?— DM Hey, DM. We were going to start today\u2019s column off with some clever and witty remark about red-letter dates, […]<\/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":[48,49,3,5],"class_list":["post-69493","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have a Microsoft Excel spreadsheet I periodically analyze using a script. How can I change the color of a cell to red if the date stored in that cell has passed?— DM Hey, DM. We were going to start today\u2019s column off with some clever and witty remark about red-letter dates, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69493","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=69493"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69493\/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=69493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=69493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=69493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}