changing a cell to different colors

[jbobak]

I have an excel worksheet that i am trying to code to make it cycle through 3
different colors (yellow, green and red) by double clicking on the cell, all
it does is keep it blank....here is the code that i have in the sheet right
now. As well i would like this to apply to only certain cells, (i dont know
how to define the range, its been a really long time since i worked with this)
HELP!!!


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If ActiveCell.Interior.ColorIndex = xlNone Then
ActiveCell.Interior.ColorIndex = 6
End If
If ActiveCell.Interior.ColorIndex = 6 Then
ActiveCell.Interior.ColorIndex = 4
End If
If ActiveCell.Interior.ColorIndex = 4 Then
ActiveCell.Interior.ColorIndex = 3
End If
If ActiveCell.Interior.ColorIndex = 3 Then
ActiveCell.Interior.ColorIndex = xlNone
End If
End Sub

 

[Project Mangler]

Hi jobobak

I think your code must be cycling through all the colours and arriving back
at a blank cell. Try this:


If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 6
ElseIf Target.Interior.ColorIndex = 6 Then
Target.Interior.ColorIndex = 4
ElseIf Target.Interior.ColorIndex = 4 Then
Target.Interior.ColorIndex = 3
Else: Target.Interior.ColorIndex = 3
Target.Interior.ColorIndex = xlNone
End If

 

[Project Mangler]

If you only want certain ranges to be affected use

Set isect = Application.Intersect(Target, Range("$A$6")) << change as needed
If Not isect Is Nothing Then
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 6
ElseIf Target.Interior.ColorIndex = 6 Then
Target.Interior.ColorIndex = 4
ElseIf Target.Interior.ColorIndex = 4 Then
Target.Interior.ColorIndex = 3
Else: Target.Interior.ColorIndex = 3
Target.Interior.ColorIndex = xlNone
End If
End If