Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
"Rick Rothstein (MVP - VB)" wrote in
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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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\)" 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 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color macro
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for color coding | Excel Discussion (Misc queries) | |||
color bar with pattern macro | Charts and Charting in Excel | |||
Make text color match cell color with macro? | Excel Discussion (Misc queries) | |||
Macro - color tab | Excel Discussion (Misc queries) | |||
Color Row Macro Problem, adapted from Patrick Malloy macro | Excel Programming |