Novice Challenge 1: Color My Column

Office Programs Required

Microsoft Excel 2007

Goldfish Awarded


Deadline for Submission

Wednesday, April 22, 2009 (8:00 AM Pacific Daylight Time)

Files Required

NoviceChallenge01.xlsm in the OfficePalooza Challenge Pack.

The flashing lights and bright colors of a carnival are nothing compared to what you can do in Microsoft Office Excel. You’ll get a little taste of that in this challenge.

When you open the file NoviceChallenge01.xlsm you’ll see a single worksheet with four colors listed in column A: red, yellow, green, and blue. To successfully complete this challenge, you must insert code that changes the color of column B based on the value of the active cell. For example, if the cell with the word red is the active cell then column B should be red. Like this:

Novice Challenge 1 

Sounds easy enough, right? The trick is that the color needs to change automatically each time a new cell becomes the active cell. (Hint: There’s a reason we specified the Worksheet_SelectionChange subroutine as the place to put your code.) If the active cell does not contain the name of a color then you need to remove the background from column B. In other words, you don’t want to click on cell A7 and have column B still showing as red.

When you open the Visual Basic editor (which you can do by pressing Alt+F11), there should already be a window open with the subroutine Worksheet_SelectionChange showing. Insert your code into this subroutine. (If you don’t see this subroutine, double-click on Sheet1 in the Project pane on the left under VBAProject (NoviceChallenge01.xlsm).) Do not create any other subroutines – all your code should go in here.

Important: Do not add code to save the spreadsheet.

Submitting Your Entry

The OfficePalooza sweepstakes is over, but you’re welcome to try the challenges and learn on your own. Good luck!