{"id":64613,"date":"2007-06-21T00:09:00","date_gmt":"2007-06-21T00:09:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/06\/21\/how-can-i-color-every-other-row-in-an-excel-spreadsheet\/"},"modified":"2007-06-21T00:09:00","modified_gmt":"2007-06-21T00:09:00","slug":"how-can-i-color-every-other-row-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-color-every-other-row-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Color Every Other Row in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! When I create a new Excel spreadsheet, I\u2019d like to color every other row in that spreadsheet. How can I do that?

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

Hey, TM. We know a lot of you have been wondering, \u201cGee, what\u2019s the matter with the Scripting Guy who writes that column? He just doesn\u2019t seem like his usual, jolly old self these days.\u201d Sadly, that\u2019s true. And there\u2019s a reason for that: while in Orlando for TechEd 2007<\/B><\/A>, the Scripting Guy who writes this column had both his confidence and his ego shattered in one crushing blow.<\/P>\n

At one point during the festivities, the Scripting Guy who writes this column left the Scripting Guys\u2019 booth in an attempt to find a place where the dynamic duo could store a box for a few hours. (How hard could it be to find a place to store one little box in a building the size of the Orlando Convention Center<\/B><\/A>? Surprisingly hard. And yet, nowhere near as hard as it was trying to get that one little box back out<\/I> of storage.) When the Scripting Guy who writes this column returned from his quest, Scripting Guy Jean Ross was busy chatting with someone. \u201cOh,\u201d said this someone as he returned to the booth. \u201cThis must be the other Scripting Guy.\u201d<\/P>\n

The other<\/I> Scripting Guy!?!? The Scripting Guy who writes this column single-handedly invented<\/I> the Script Center, for goodness sake! How many Script Centers do you suppose Scripting Guy Jean Ross has invented? That\u2019s right: zero. And yet Scripting Guy Jean Ross \u2013 who, we might point out, isn\u2019t even a guy<\/I> \u2013 has somehow become the<\/I> Scripting Guy. And the Scripting Guy who writes this column? He has somehow become the other<\/I> Scripting Guy.<\/P>\n

The other Scripting Guy. Was George Harrison the \u201cother\u201d Beatle to John and Paul? Was Chico the \u201cother\u201d Marx Brother to Groucho? Was \u2013 well, never mind. You know what we mean.<\/P>\n

Of course, people are truly defined by how they react to adversity. The Scripting Guy who writes this column reacted to adversity by getting very depressed and withdrawing into his office, his life pretty much over. What\u2019s that? Write a really good column, one that, say, colors every other row in an Excel spreadsheet. You say that will show Jean Ross who the real<\/I> Scripting Guy is? You know what? You\u2019re right: that would<\/I> show Jean who the real Scripting Guy is, wouldn\u2019t it? <\/P>\n

On the other hand, that sounds like a lot of work, and it sounds kind of hard, to boot. Sorry; we\u2019ll pass on that.<\/P>\n

Unless \u2026 Guess what? In rummaging through Jean\u2019s desk, looking for a \u2026 paperclip \u2026 we just happened to run across a script that can color every other row in a spreadsheet. The other<\/I> Scripting Guy? Not anymore, Jean Ross:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
For i = 1 to 5\n    If i Mod 2 = 0 Then\n        Set objRange = objExcel.ActiveCell.EntireRow\n        objRange.Cells.Interior.ColorIndex = 37\n    Else\n        Set objRange = objExcel.ActiveCell.EntireRow\n        objRange.Cells.Interior.ColorIndex = 36\n    End If<\/p>\n
    objWorksheet.Cells(i,1) = i<\/p>\n
    intNewRow = objExcel.ActiveCell.Row + 1\n    strNewCell = “A” &  intNewRow\n    objExcel.Range(strNewCell).Activate<\/p>\n
Next\n<\/PRE>\n
OK, so how does this script work? (Good question; unfortunately, Jean didn\u2019t seem to leave an explanation lying around anywhere. We\u2019ll have to look through her purse the next time she goes to the bathroom.) As near as we can tell, the script starts out by creating an instance of the Excel.Application<\/B> object, and then sets the Visible<\/B> property to True; that gives us a running instance of Excel that we can see onscreen. Jean then \u2013 um, we<\/I> then use the Add<\/B> method to add a new workbook, then use this line of code to bind to the first worksheet in the workbook:<\/P>
Set objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
That was easy enough, wasn\u2019t it? OK. In our sample spreadsheet, we\u2019re going to put data in the first five rows; therefore, we set up a little For Next loop that runs from 1 to 5:<\/P>
For i = 1 to 5\n<\/PRE>\n
Inside that loop, the very first thing we do is execute this line of code:<\/P>
If i Mod 2 = 0 Then\n<\/PRE>\n
What does this line of code do? Well, it \u2026 um, it \u2013 hold on second, we need to go ask Jean a question \u2026..<\/P>\n
OK, we\u2019re back. As we were saying \u2013 and as any real<\/I> Scripting Guy knows \u2013 the Mod<\/B> function returns the remainder of a division problem. All we\u2019re doing here is dividing the counter variable i by 2 and then checking to see if the remainder is 0. Why<\/I> do we do that? Just a second \u2026 we do that as a way to keep track of alternating rows. Odd-numbered rows (1, 3, 5, 7, etc.) will not<\/I> have a remainder of 0; that\u2019s because 2 doesn\u2019t divide evenly into an odd number. Even-numbered rows, by comparison, will<\/I> have a remainder of 0. Even-odd, even-odd, even-odd: a simple way to keep track of alternating rows.<\/P>\n
So what if the remainder is<\/I> 0? In that case we\u2019re going to execute this block of code:<\/P>
Set objRange = objExcel.ActiveCell.EntireRow\nobjRange.Cells.Interior.ColorIndex = 37\n<\/PRE>\n
As you can see, we\u2019re doing two things here. First, we\u2019re creating a range object that encompasses all the cells in row 1. How do we know<\/I> that this range object encompasses all the cells in row 1? Hey, even the Scripting Guy who writes this column knows the answer to that one. With this line of code we\u2019re referencing the EntireRow<\/B> property for the row containing the ActiveCell<\/B> (the cell that currently has the cursor in it). By default, any time you programmatically create a new spreadsheet the cursor gets placed in cell A1. And because cell A1 is in row 1, then that must mean we\u2019re selecting all the cells in row 1.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Take that<\/I>, Scripting Guy Jean Ross!<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
In the second line of code we then set the background color (Interior.ColorIndex<\/B>) of all the cells in row 1 to a light yellow. How did we know that the value 37 would result in a light yellow? That\u2019s also an easy one: for some reason, Jean Ross has all the Excel color values tattooed to her forearm. We just waited until she rolled up her sleeves and then took a quick peek. If you can\u2019t see Jean\u2019s forearms from where you are, don\u2019t panic: there is<\/I> an alternative. For more information, see this Office Space article<\/B><\/A>.<\/P>\n
Now, what if the remainder isn\u2019t<\/I> 0? Well, in that case we could just leave the row uncolored. But that\u2019s what someone like Scripting Guy Jean Ross would do. A real<\/I> Scripting Guy would color the odd-numbered rows a light blue, using this block of code:<\/P>
Set objRange = objExcel.ActiveCell.EntireRow\nobjRange.Cells.Interior.ColorIndex = 36\n<\/PRE>\n
After coloring the row the appropriate shade, we then assign the value i to the cell in column A of the current row:<\/P>
objWorksheet.Cells(i,1) = i\n<\/PRE>\n
That\u2019s no big deal; we just do that to make sure our spreadsheet has some data in it. We then run this block of code:<\/P>
intNewRow = objExcel.ActiveCell.Row + 1\nstrNewCell = “A” &  intNewRow\nobjExcel.Range(strNewCell).Activate\n<\/PRE>\n
Why this<\/I> block of code? Well, according to Jean, programmatically adding data to a new row doesn\u2019t cause the active cell to change; if we don\u2019t explicitly make a change then cell A1 will remain the active cell forever and ever. That wouldn\u2019t be very good; if that happened we\u2019d end up doing nothing more than changing the background color of row 1 over and over again. Therefore, we do the following instead.<\/P>\n
First, we assign the value of the active cell\u2019s Row<\/B> plus 1 to a variable named intNewRow. Why \u201cplus 1?\u201d Well, let\u2019s assume that the active cell is currently in row 1; that means we need to move the active cell to row 2. And because 1 + 1 is \u2013 we\u2019ll be right back; hey, Jean, what\u2019s 1 + 1? Anyway, because 1 + 1 is 2, well \u2026.<\/P>\n
We then combine the new row number with the letter A, assigning that value to a variable named strNewCell:<\/P>
strNewCell = “A” &  intNewRow\n<\/PRE>\n
Needless to say, the first time through the loop that will make strNewCell equal to A2. That means that we can then call the Activate<\/B> method and make cell strNewCell (A2) the active cell:<\/P>
objExcel.Range(strNewCell).Activate\n<\/PRE>\n
From there we loop around and repeat the entire process with the next row in the worksheet.<\/P>\n
When all is said and done we\u2019ll end up with a worksheet that looks remarkably like this:<\/P>\"Microsoft \n

And we did it without any help from Jean Ross.<\/P>\n
Pretty much.<\/P>\n
By the way, we should point out that we have nothing against Scripting Guy Jean Ross; heck, some of our best friends are named Scripting Guy Jean Ross. It\u2019s just that we don\u2019t see what Jean has done to merit the title of the<\/I> Scripting Guy. OK, granted, in the first year after she joined the team our page views more than doubled; that\u2019s definitely impressive. And sure, Dr. Scripto\u2019s Fun Book<\/B><\/A>, which was one of the most sought-after giveaways at TechEd, was entirely her idea. And, fine: she\u2019s the only Scripting Guy capable of keeping the Script Center going from day-to-day. But other than that \u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! When I create a new Excel spreadsheet, I\u2019d like to color every other row in that spreadsheet. How can I do that?— TM Hey, TM. We know a lot of you have been wondering, \u201cGee, what\u2019s the matter with the Scripting Guy who writes that column? He just doesn\u2019t seem like his […]<\/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-64613","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! When I create a new Excel spreadsheet, I\u2019d like to color every other row in that spreadsheet. How can I do that?— TM Hey, TM. We know a lot of you have been wondering, \u201cGee, what\u2019s the matter with the Scripting Guy who writes that column? He just doesn\u2019t seem like his […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64613","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=64613"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64613\/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=64613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64613"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}