| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling background colors
When the user clicks any cell within one of the ranges, the background color
(by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
Hi Tom,
Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
So define the variable. You have Option Explicit turned on :
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim arr as variant, arr1 as variant Dim i as long If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub Seems like you have been doing this long enough to work that out??? -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
Thanks, Tom. You're right, I will study and practice more on dim. I pasted
the revised code into sheet 1 VB, and tried clicking on a cell in one of the ranges, with no response. Is this the correct place for the code? I manually put a background color in a cell to test return to white (both versions: xlNone and 2) with no response. To make sure I'm telling you this correctly, I simply click in a cell, with the expectation of the color change. Phil "Tom Ogilvy" wrote: So define the variable. You have Option Explicit turned on : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim arr as variant, arr1 as variant Dim i as long If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub Seems like you have been doing this long enough to work that out??? -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
Tom,
This is working okay - one of the ranges was absent from the code - I entered it and all is well. Thanks much for the help "Tom Ogilvy" wrote: So define the variable. You have Option Explicit turned on : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim arr as variant, arr1 as variant Dim i as long If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub Seems like you have been doing this long enough to work that out??? -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
I right clicked on the sheet tab and selected view code and wrote the code
in that module. I worked fine for me. If you need a sample workbook, post your email address. -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Thanks, Tom. You're right, I will study and practice more on dim. I pasted the revised code into sheet 1 VB, and tried clicking on a cell in one of the ranges, with no response. Is this the correct place for the code? I manually put a background color in a cell to test return to white (both versions: xlNone and 2) with no response. To make sure I'm telling you this correctly, I simply click in a cell, with the expectation of the color change. Phil "Tom Ogilvy" wrote: So define the variable. You have Option Explicit turned on : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim arr as variant, arr1 as variant Dim i as long If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub Seems like you have been doing this long enough to work that out??? -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
one of the ranges was absent from the code
I don't claim to be perfect <g I know why it was missing, but it would take too long to explain. Glad you got it working. -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Tom, This is working okay - one of the ranges was absent from the code - I entered it and all is well. Thanks much for the help "Tom Ogilvy" wrote: So define the variable. You have Option Explicit turned on : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim arr as variant, arr1 as variant Dim i as long If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub Seems like you have been doing this long enough to work that out??? -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
Tom, Can we expand this to have borders: index 56 when colored, index 15
when not colored? "Tom Ogilvy" wrote: So define the variable. You have Option Explicit turned on : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim arr as variant, arr1 as variant Dim i as long If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub Seems like you have been doing this long enough to work that out??? -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i as Long Dim arr as Variant, arr1 as Variant If Target.Count 1 Then Exit Sub arr = Array("S8:AX16", "S17:AX23", _ "S24:AX34", "S36:AX38") arr1 = Array(36, 40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone Target.BorderAround ColorIndex:=56 ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) Target.BorderAround ColorIndex:=15 End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Tom, Can we expand this to have borders: index 56 when colored, index 15 when not colored? "Tom Ogilvy" wrote: So define the variable. You have Option Explicit turned on : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim arr as variant, arr1 as variant Dim i as long If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub Seems like you have been doing this long enough to work that out??? -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggling background colors
Thanks, Tom. Works great!
"Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim i as Long Dim arr as Variant, arr1 as Variant If Target.Count 1 Then Exit Sub arr = Array("S8:AX16", "S17:AX23", _ "S24:AX34", "S36:AX38") arr1 = Array(36, 40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone Target.BorderAround ColorIndex:=56 ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) Target.BorderAround ColorIndex:=15 End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Tom, Can we expand this to have borders: index 56 when colored, index 15 when not colored? "Tom Ogilvy" wrote: So define the variable. You have Option Explicit turned on : Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim arr as variant, arr1 as variant Dim i as long If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub Seems like you have been doing this long enough to work that out??? -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Got a compile error - variable not defined. On line three, starting "arr = Array...," "arr_" is highlighted. Phil "Tom Ogilvy" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub arr = Array("S17:AX23", "S24:AX34", "S36:AX38") arr1 = Array(40, 34, 35) For i = LBound(arr) To UBound(arr) If Not Intersect(Range(arr(i)), Target) Is Nothing Then If Target.Interior.ColorIndex = arr1(i) Then Target.Interior.ColorIndex = xlNone ' or ' Target.Interior.ColorIndex = 2 Else Target.Interior.ColorIndex = arr1(i) End If Exit For End If Next End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... When the user clicks any cell within one of the ranges, the background color (by color index, or any other appropriate color selection system) is selected. When the user clicks a background-colored cell, the color returns to White, index 2. We are toggling a color on and off, based on cell location and color index. What would the code be? Range Index S8:AX16 36 S17:AX23 40 S24:AX34 34 S36:AX38 35 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Chart background colors by value | Charts and Charting in Excel | |||
| Formatting background colors | Excel Worksheet Functions | |||
| functions with background colors | Excel Worksheet Functions | |||
| Lost highlighting and font colors; background colors on web pages | Excel Discussion (Misc queries) | |||
| Alternate Background-colors | Excel Discussion (Misc queries) | |||
![Phil Hageman[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
