Color macro

[Little Penny]

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

 

[Gord Dibben]

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

 

[Chip Pearson]

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)

 

[Guest]

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

 

[Guest]

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?

 

[Dave Peterson]

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

 

[Rick Rothstein \(MVP - VB\)]

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.

 

[Peter T]

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

 

[Peter T]

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

 

[Rick Rothstein \(MVP - VB\)]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel

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

 

[Little Penny]

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

 

[Rick Rothstein \(MVP - VB\)]

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