Color macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Little Penny - 23 Sep 2007 00:35 GMT

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

Reply to this Message

Gord Dibben - 23 Sep 2007 01:12 GMT

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

>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

Reply to this Message

Chip Pearson - 23 Sep 2007 01:20 GMT

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

Signature

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

> 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

Reply to this Message

Jim May - 23 Sep 2007 01:52 GMT

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?

> Right click the appropriate sheet tab, choose View Code, and paste in the
> following:
[quoted text clipped - 19 lines]
> >
> > Thanks

Reply to this Message

Dave Peterson - 23 Sep 2007 02:14 GMT

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

> 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
[quoted text clipped - 31 lines]
> > >
> > > Thanks

Signature

Dave Peterson

Reply to this Message

Peter T - 23 Sep 2007 09:50 GMT

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

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

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Sep 2007 05:14 GMT

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)
[quoted text clipped - 10 lines]
> 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.

Reply to this Message

Peter T - 23 Sep 2007 10:07 GMT

> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> > Boolean)
[quoted text clipped - 16 lines]
> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Sep 2007 15:45 GMT

>> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
>> > As
[quoted text clipped - 22 lines]
> 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

Reply to this Message

Little Penny - 23 Sep 2007 16:18 GMT

Rick I sorry but I did not read your email because its a email adress
I no longer use. For over a year now I have undated my email address.

I sorry

On Sun, 23 Sep 2007 00:14:01 -0400, "Rick Rothstein $MVP - VB$"
<rickNOSPAMnews@NOSPAMcomcast.net> wrote:

>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
[quoted text clipped - 30 lines]
>asked in them, that is fine, no problem; but I was wondering if you even got
>emails in the first place.

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Sep 2007 17:04 GMT

> Rick I sorry but I did not read your email because its a email adress
> I no longer use. For over a year now I have undated my email address.

I'm not sure why you posted this message... I did not attempt to send you an
email... my only responses dealing with your question were posted here, in
this thread.

Rick

Reply to this Message

FSt1 - 23 Sep 2007 01:26 GMT

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

> 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

Reply to this Message