.ColorIndex: 3rd color by clicking cell?

**Durban** · 06-14-2010, 09:45 PM

Hey!

following problem: I want to change the background-color of a cell by just clicking it. I managed that by using the following code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range
Set myRange = Application.Union(Range("A1:A3"), Range("B1:B2"))
If Intersect(Target, myRange) Is Nothing Then Exit Sub

If Target.Interior.ColorIndex = 1 Then
With Selection.Interior
.ColorIndex = 3
End With
Exit Sub
End If

With Selection.Interior
.ColorIndex = 1
End With

End Sub

So, when I click a cell within the range, it's color changes to black, 2nd click: from black to red, 3rd click: black again and so on...But, what do I do when I wanna have a third color (3rd click)? e.g. the loop would be "black, red, blue, black, ..." instead of "black, red, black, red..."?
Any1 can help me?

**Deamo** · 06-14-2010, 11:57 PM

simplist solution i can come up with - changing your if statements

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range
Set myRange = Application.Union(Range("A1:A3"), Range("B1:B2"))
If Intersect(Target, myRange) Is Nothing Then Exit Sub

With Selection.Interior
    If Target.Interior.ColorIndex = 1 Then
        .ColorIndex = 3
    ElseIf .ColorIndex = 3 Then
        .ColorIndex = 8
        Exit Sub
    Else: .ColorIndex = 1
    End If
End With
End Sub

Edit: Alternatively, a case statement (which would allow more colours with less code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range
Set myRange = Application.Union(Range("A1:A3"), Range("B1:B2"))
If Intersect(Target, myRange) Is Nothing Then Exit Sub

Select Case Selection.Interior.ColorIndex
    Case 1: Selection.Interior.ColorIndex = 3
    Case 3: Selection.Interior.ColorIndex = 8
    Case Else: Selection.Interior.ColorIndex = 1
End Select
End Sub