Is there a macro that will change the interior color of a cell on a
click or double click? And then change it back to the default if
clicked again. I want to be able to go back and forth.


Thanks

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Const myRange As String = "A1:A10"
On Error GoTo endit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then
With Target
If .Interior.ColorIndex = 3 Then
.Interior.ColorIndex = xlNone
Else
.Interior.ColorIndex = 3
End If
End With
Cancel = True 'preserve double-click edit for cells not in MyRange
End If
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Adjust MyRange to suit.


Gord Dibben MS Excel MVP


On Sat, 22 Sep 2007 19:35:56 -0400, Little Penny <(E-Mail Removed)>
wrote:

>Is there a macro that will change the interior color of a cell on a
>click or double click? And then change it back to the default if
>clicked again. I want to be able to go back and forth.
>
>
>Thanks

Right click the appropriate sheet tab, choose View Code, and paste in the
following:


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).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Little Penny" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Is there a macro that will change the interior color of a cell on a
> click or double click? And then change it back to the default if
> clicked again. I want to be able to go back and forth.
>
>
> Thanks

hi
this is worksheet code. right click the sheet tab and click view code.
the worksheet change event is the default. delete it and paste this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If ActiveCell.Interior.ColorIndex = xlNone Then
ActiveCell.Interior.ColorIndex = 40 ' tan...sort of
Else
ActiveCell.Interior.ColorIndex = xlNone
End If
End Sub

see this site for other color indexes.
http://www.mvps.org/dmcritchie/excel/colors.htm

regards
FSt1

"Little Penny" wrote:

> Is there a macro that will change the interior color of a cell on a
> click or double click? And then change it back to the default if
> clicked again. I want to be able to go back and forth.
>
>
> Thanks
>

This (code) seems to be removing the grid/border lines. Can this be also
handled to maintain the original look, including the borders/grid lines
arounf the cell?

"Chip Pearson" wrote:

> Right click the appropriate sheet tab, choose View Code, and paste in the
> following:
>
>
> 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).
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
>
> "Little Penny" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Is there a macro that will change the interior color of a cell on a
> > click or double click? And then change it back to the default if
> > clicked again. I want to be able to go back and forth.
> >
> >
> > Thanks
>

I don't think it's touching the borders (format|Cell|border).

But if you add fill colors to cells, then the gridlines
(tools|Options|view tab|gridlines)
will seem to disappear.

But that happens no matter how you apply that fill color.

Another good reason to not show the gridlines and use borders instead <bg>.

Jim May wrote:
>
> This (code) seems to be removing the grid/border lines. Can this be also
> handled to maintain the original look, including the borders/grid lines
> arounf the cell?
>
> "Chip Pearson" wrote:
>
> > Right click the appropriate sheet tab, choose View Code, and paste in the
> > following:
> >
> >
> > 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).
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting
> > www.cpearson.com
> > (email on the web site)
> >
> >
> > "Little Penny" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Is there a macro that will change the interior color of a cell on a
> > > click or double click? And then change it back to the default if
> > > clicked again. I want to be able to go back and forth.
> > >
> > >
> > > Thanks
> >

--

Dave Peterson

The technique in my comment below can actually be applied to each of the
respondents so far; however, I have a question for you directly (which is in
the PS at the end of my message)...

> 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

I used a line continuation to prevent newsreaders from splitting the line in
an inappropriate location, but it is a one-liner.

Rick

PS - Did you receive any email messages from me last month or at the
beginning of this month? If you don't want to respond to the question I
asked in them, that is fine, no problem; but I was wondering if you even got
emails in the first place.

Can simulate the gridlines that become hidden with the fill colour by adding
similar looking grey borders

With Target.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(192, 192, 192)
'or if sure using a default palette
'.ColorIndex = 15
End With

remove with
Target.Borders.Colorindex = xlNone

Before applying the above might want to check user has not already applied
own border to one or more edges.

Regards,
Peter T

"Jim May" <(E-Mail Removed)> wrote in message
news:31019C18-B3CD-4FBA-A31B-(E-Mail Removed)...
> This (code) seems to be removing the grid/border lines. Can this be also
> handled to maintain the original look, including the borders/grid lines
> arounf the cell?
>

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:O5nRjgZ$(E-Mail Removed)...

> > 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.

Regards,
Peter T

>> > 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