Color macro
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
If Target.Address = "$A$1" Then '<<<< CHANGE ADDRESS
If Target.Interior.ColorIndex = 6 Then '<<< 6 = yellow
Target.Interior.ColorIndex = xlColorIndexAutomatic
Else
Target.Interior.ColorIndex = 6
End If
Cancel = True
End If
End Sub
Change the address from $A$1 to the appropriate cell and change the 6
to the desired ColorIndex value (see VBA Help for a list of colors).
Using your example color index of 6, your interior If-Then-Else block can
be replaced with this one-liner code...
Target.Interior.ColorIndex = (6 + xlColorIndexAutomatic) - _
Target.Interior.ColorIndex
This might be OK if can be certain Target.Interior.ColorIndex is either 6
or
xlColorIndexAutomatic -4105. But if it's xlNone -4142 (default no fill) or
not 6 it may fail completely or apply 6+(-4105)-(-4142) ie 43.
The OP's initial posting said that he wanted to toggle back and forth
between a color of his/her choice and the default color. The way the message
was worded seemed to indicate the cell were already one of these colors from
the start. But, with that said, you might be right (the starting color could
be starting off different from either of these), so your warning is
something I should have thought to include in my response... thanks for
doing so.
Rick
|