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