Solution - Novice Challenge 1: Color My Column
We gave you a good start in this challenge by having you put the code in the Worksheet_SelectionChange subroutine. This subroutine runs each time a new cell is selected. Your solution may be different, but here’s how we solved this challenge:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
'Take action based on which color was selected.
Select Case LCase(Target.Value)
Case "red"
Range("B1").EntireColumn.Interior.ColorIndex = 3
Case "yellow"
Range("B1").EntireColumn.Interior.ColorIndex = 6
Case "green"
Range("B1").EntireColumn.Interior.ColorIndex = 4
Case "blue"
Range("B1").EntireColumn.Interior.ColorIndex = 5
Case Else
Range("B1").EntireColumn.Interior.ColorIndex = xlColorIndexNone
End Select
End Sub
As you can see, it didn’t take a lot of code to solve this challenge. (We’ll show you an alternate solution in a moment that takes even less code.) Notice in the subroutine declaration that a variable named Target is passed to the subroutine:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
We already know that this subroutine is called every time the selected cell changes. When that happens, the cell, or range, is passed to the subroutine and stored in the variable Target. For example, if cell A1 is selected, Target contains a Range object representing cell A1.
The first thing we do inside the subroutine is to call the On Error Resume Next statement. Throughout these challenges you won’t see a lot of error handling – this is so we can keep the explanations simpler and focus on the solutions. However, in this case we need this statement to avoid a very common occurrence that could cause problems with our code. This solution relies on the fact that one of the cells with the name of a color in it has been chosen. But what if a range of cells has been chosen, not just a single cell? This would raise an error later in our code. Rather than check for that, we simply ignore it, which is what On Error Resume Next allows us to do.
Next we insert a Select Case statement. A Select Case statement allows us to take an action based on the value of a particular variable. In this case we’re testing the Value contained in the selected Range object:
Select Case LCase(Target.Value)
Notice we put the value within the LCase method. The LCase method turns the characters in any string passed to it into all lowercase letters. In other words, if the value of the cell was Red, LCase would change that to red. We didn’t really need to do this (and you didn’t need to do this in order to successfully solve the challenge), but this does make our code a little bit more flexible. We’re going to compare this value to a string that contains the name of a color, and by first calling LCase we don’t have to assume that the color name will always be entered in the spreadsheet as a lowercase value.
The standard color palette in Microsoft Office Excel contains 56 colors, each with a different color index numbered 1 through 56. Where do you find these values? Just do a search on MSDN on color values Excel and you should come across something like this: https://msdn.microsoft.com/en-us/library/cc296089.aspx; there you’ll find a table showing the 56-color palette and the index value of each color. That’s where our next section of code comes in:
Case "red"
Range("B1").EntireColumn.Interior.ColorIndex = 3
Case "yellow"
Range("B1").EntireColumn.Interior.ColorIndex = 6
Case "green"
Range("B1").EntireColumn.Interior.ColorIndex = 4
Case "blue"
Range("B1").EntireColumn.Interior.ColorIndex = 5
Case Else
Range("B1").EntireColumn.Interior.ColorIndex = xlColorIndexNone
In the Select statement we checked the lowercase value in the selected cell. Now we’re comparing that value to the strings in the Case statements (“red,” “yellow,” etc.). If, for example, the selected cell contained the value red, the code following the Case “red” statement would run:
Range("B1").EntireColumn.Interior.ColorIndex = 3
And what does this line of code do? It turns column B red. How does it do that? Well, let’s take a look at this line one piece at a time. First we specify the range we want to work with: Range(“B1”).
Range("B1") .EntireColumn.Interior.ColorIndex = 3
We’re going to change the color in the entire B column, not just in cell B1, so we need to select the entire column in which cell B1 is located. This is where the EntireColumn property comes in.
Range("B1").EntireColumn.Interior.ColorIndex = 3
We’re going to be coloring the inside of the cells, so we next retrieve the Interior object:
Range("B1").EntireColumn.Interior.ColorIndex = 3
And finally, we’re setting the ColorIndex property of the Interior object:
Range("B1").EntireColumn.Interior.ColorIndex = 3
The color table shows us that red has a color index of 3, so that’s what we set the ColorIndex to.
That wasn’t too bad, was it? But we had one last requirement for this challenge. When a cell was selected that didn’t have a color in it, we needed to remove the color from column B. We do that with this final Case statement:
Case Else
Range("B1").EntireColumn.Interior.ColorIndex = xlColorIndexNone
If none of the colors we checked for is found, then we fall into the Else part of the Select Case statement. Here we set the ColorIndex property of column B to xlColorIndexNone. This is a system-defined constant that removed the color index and returns the cell to the system color.
That’s all there is to solving this challenge. What about that solution we mentioned that has even less code? Here you go:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
'Take action based on which color was selected.
With Range("B1").EntireColumn.Interior
Select Case LCase(Target.Value)
Case "red"
.ColorIndex = 3
Case "yellow"
.ColorIndex = 6
Case "green"
.ColorIndex = 4
Case "blue"
.ColorIndex = 5
Case Else
.ColorIndex = xlColorIndexNone
End Select
End With
End Sub
This is really the exact same code we just walked through, but we’ve added a With statement. Instead of typing outRange("B1").EntireColumn.Interior.ColorIndex each time we want to set the color index of column B, we only have to type out once. Anything within the With statement that starts with a dot (such as .ColorIndex) will be assumed to be preceded by Range("B1").EntireColumn.Interior. That just makes our code a little cleaner and reduces our typing by a few keystrokes.
And that’s it for Challenge 1. We’re not saying this is the best possible solution, and your solution may have been different. But as long as it worked you received the goldfish.