Newbie: Problem with 'Select Case' testing syntax

[Thomas Toth]

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

 

[Mike H]

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

 

[OssieMac]

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]

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

 

[Thomas Toth]

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

 

[papou]

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]

Hi Pascal,

So simple yet perfectly right Should've thought of it myself.

Thanks a lot for your help,
Thomas

 

[Thomas Toth]

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

 

[Dave Peterson]

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]

Hi Dave,

Perfect solution, works like a charm.

Thanks a lot,
Thomas