| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie: Problem with 'Select Case' testing syntax
Hi,
I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
Try this
Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("a1:a10")) Is Nothing Then Select Case Target.Value Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub Mike "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
Hi Thomas,
You should be testing a variable like Target not the value true. You don't use the equal signs. Also I don't think that Like works. Select Case Target Case "c", "C" IntColour = 50 Case Like "??" 'Don't think that this works IntColour = 3 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If Regards, OssieMac "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
Hi OssieMac,
the reason for testing True is to be able to use Like cause it won't work in a normal Case statement. This is the version I had and which I'm trying to modify to be able to use wild cards in the test statement. Thanks for you help, Thomas OssieMac wrote: Hi Thomas, You should be testing a variable like Target not the value true. You don't use the equal signs. Also I don't think that Like works. Select Case Target Case "c", "C" IntColour = 50 Case Like "??" 'Don't think that this works IntColour = 3 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If Regards, OssieMac "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
Hi Mike,
your addition does not seem to have improved the situation. While the "1 To 9" now works the "Like" stopped working and for some reason only 'C' works but not 'c'. Thanks for your help, Thomas Mike H wrote: Try this Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("a1:a10")) Is Nothing Then Select Case Target.Value Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub Mike "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
Hello Thomas
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Dim IntColour As Integer If Len(Target.Value) = 2 Then IntColour = 3 Else Select Case Target.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If Target.Interior.ColorIndex = IntColour End If End Sub HTH Cordially Pascal "Thomas Toth" a écrit dans le message de news: ... Hi OssieMac, the reason for testing True is to be able to use Like cause it won't work in a normal Case statement. This is the version I had and which I'm trying to modify to be able to use wild cards in the test statement. Thanks for you help, Thomas OssieMac wrote: Hi Thomas, You should be testing a variable like Target not the value true. You don't use the equal signs. Also I don't think that Like works. Select Case Target Case "c", "C" IntColour = 50 Case Like "??" 'Don't think that this works IntColour = 3 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If Regards, OssieMac "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
Hi Pascal,
So simple yet perfectly right :) Should've thought of it myself. Thanks a lot for your help, Thomas papou wrote: Hello Thomas Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Dim IntColour As Integer If Len(Target.Value) = 2 Then IntColour = 3 Else Select Case Target.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If Target.Interior.ColorIndex = IntColour End If End Sub HTH Cordially Pascal "Thomas Toth" a écrit dans le message de news: ... Hi OssieMac, the reason for testing True is to be able to use Like cause it won't work in a normal Case statement. This is the version I had and which I'm trying to modify to be able to use wild cards in the test statement. Thanks for you help, Thomas OssieMac wrote: Hi Thomas, You should be testing a variable like Target not the value true. You don't use the equal signs. Also I don't think that Like works. Select Case Target Case "c", "C" IntColour = 50 Case Like "??" 'Don't think that this works IntColour = 3 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If Regards, OssieMac "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
Hi Pascal,
there is actually one problem with your solution. If I select more than 1 cell, eg to copy-paste content, then I get an error with the "If Len...". This IF does not seem to be compatible with multiple selected cells. Any ideas where this comes from and how to get around it? Thanks a lot, Thomas papou wrote: Hello Thomas Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Dim IntColour As Integer If Len(Target.Value) = 2 Then IntColour = 3 Else Select Case Target.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If Target.Interior.ColorIndex = IntColour End If End Sub HTH Cordially Pascal "Thomas Toth" a écrit dans le message de news: ... Hi OssieMac, the reason for testing True is to be able to use Like cause it won't work in a normal Case statement. This is the version I had and which I'm trying to modify to be able to use wild cards in the test statement. Thanks for you help, Thomas OssieMac wrote: Hi Thomas, You should be testing a variable like Target not the value true. You don't use the equal signs. Also I don't think that Like works. Select Case Target Case "c", "C" IntColour = 50 Case Like "??" 'Don't think that this works IntColour = 3 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If Regards, OssieMac "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
You could check to see how many cells were changed and get out if there is more
than one: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Long If Intersect(Target, Me.Range("D9:Z14")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) = 2 Then IntColour = 3 Else Select Case Target.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If Target.Interior.ColorIndex = IntColour End Sub Or you could look at each of the cells in the intersection of the target and the range you want to inspect: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Long Dim myCell As Range Dim myRng As Range Set myRng = Intersect(Target, Me.Range("D9:Z14")) If myRng Is Nothing Then Exit Sub For Each myCell In myRng.Cells If Len(myCell.Value) = 2 Then IntColour = 3 Else Select Case myCell.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If myCell.Interior.ColorIndex = IntColour Next myCell End Sub Thomas Toth wrote: Hi Pascal, there is actually one problem with your solution. If I select more than 1 cell, eg to copy-paste content, then I get an error with the "If Len...". This IF does not seem to be compatible with multiple selected cells. Any ideas where this comes from and how to get around it? Thanks a lot, Thomas papou wrote: Hello Thomas Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Dim IntColour As Integer If Len(Target.Value) = 2 Then IntColour = 3 Else Select Case Target.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If Target.Interior.ColorIndex = IntColour End If End Sub HTH Cordially Pascal "Thomas Toth" a écrit dans le message de news: ... Hi OssieMac, the reason for testing True is to be able to use Like cause it won't work in a normal Case statement. This is the version I had and which I'm trying to modify to be able to use wild cards in the test statement. Thanks for you help, Thomas OssieMac wrote: Hi Thomas, You should be testing a variable like Target not the value true. You don't use the equal signs. Also I don't think that Like works. Select Case Target Case "c", "C" IntColour = 50 Case Like "??" 'Don't think that this works IntColour = 3 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If Regards, OssieMac "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie: Problem with 'Select Case' testing syntax
Hi Dave,
Perfect solution, works like a charm. Thanks a lot, Thomas Dave Peterson wrote: You could check to see how many cells were changed and get out if there is more than one: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Long If Intersect(Target, Me.Range("D9:Z14")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) = 2 Then IntColour = 3 Else Select Case Target.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If Target.Interior.ColorIndex = IntColour End Sub Or you could look at each of the cells in the intersection of the target and the range you want to inspect: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Long Dim myCell As Range Dim myRng As Range Set myRng = Intersect(Target, Me.Range("D9:Z14")) If myRng Is Nothing Then Exit Sub For Each myCell In myRng.Cells If Len(myCell.Value) = 2 Then IntColour = 3 Else Select Case myCell.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If myCell.Interior.ColorIndex = IntColour Next myCell End Sub Thomas Toth wrote: Hi Pascal, there is actually one problem with your solution. If I select more than 1 cell, eg to copy-paste content, then I get an error with the "If Len...". This IF does not seem to be compatible with multiple selected cells. Any ideas where this comes from and how to get around it? Thanks a lot, Thomas papou wrote: Hello Thomas Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Dim IntColour As Integer If Len(Target.Value) = 2 Then IntColour = 3 Else Select Case Target.Value Case "c", "C" IntColour = 50 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select End If Target.Interior.ColorIndex = IntColour End If End Sub HTH Cordially Pascal "Thomas Toth" a écrit dans le message de news: ... Hi OssieMac, the reason for testing True is to be able to use Like cause it won't work in a normal Case statement. This is the version I had and which I'm trying to modify to be able to use wild cards in the test statement. Thanks for you help, Thomas OssieMac wrote: Hi Thomas, You should be testing a variable like Target not the value true. You don't use the equal signs. Also I don't think that Like works. Select Case Target Case "c", "C" IntColour = 50 Case Like "??" 'Don't think that this works IntColour = 3 Case 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If Regards, OssieMac "Thomas Toth" wrote: Hi, I'm trying to use a case list for conditional formating and thereby I'm getting stuck with the syntax of the case conditions. Maybe someone could point out why they don't work. Here's what I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim IntColour As Integer If Not Intersect(Target, Range("D9:Z14")) Is Nothing Then Select Case True Case Target = "c", "C" IntColour = 50 Case Target Like "??" IntColour = 3 Case Target = 1 To 9 IntColour = 54 Case Else IntColour = 15 End Select Target.Interior.ColorIndex = IntColour End If End Sub The selection with 'Like' works fine, the others fail. - Why does the one with the "c","C" not work? It works when I only have "c", and it doesn't like OR either. - Why doesn't the 1 To 9 work either? It works for 1 but not for the other numbers. Any help is appreciated. Thanks a lot, Thomas |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| Select Case syntax | Excel Programming | |||
| Select Case for Active Worksheet syntax. | Excel Programming | |||
| Syntax for Select Case | Excel Programming | |||
| Select Case syntax | Excel Programming | |||
Linear Mode
