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