Select Case Target.value
Case Is < 100
Colour = 6
Case Is 500
Colour = 8
Case Is = 700
Colour = 50
Case Is = 850
Colour = 24
End Select

If the target.value = 999, then when the code runs, it'll find that it's 500
and use 8 for the colour. It won't continue to check all the other comparisons.

So try rearranging the checks in a nicer order:

Select Case Target.value
Case Is = 850
Colour = 24
Case Is = 700
Colour = 50
Case Is 500
Colour = 8
Case Is < 100
Colour = 6
End Select

What should happen if the target.value is 499?

Marilyn wrote:

Hello
I'm created the following VBA code to conditional format a section on my
spreadsheet. Below is my test code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Colour As Integer
If Not Intersect(Target, Range("a1:G75")) Is Nothing Then
Select Case Target
Case Is = "John"
Colour = 6
Case Is = "Mary"
Colour = 8
Case Is = "Jane"
Colour = 50
Case Is = "Bob"
Colour = 24
Case Else
End Select
Target.Interior.ColorIndex = Colour
End If

End Sub
the above code works fine but when I tried to adjust the code to use
numbers instead of words - it does not work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Colour As Integer
If Not Intersect(Target, Range("a1:G75")) Is Nothing Then
Select Case Target
Case Is < 100
Colour = 6
Case Is 500
Colour = 8
Case Is = 700
Colour = 50
Case Is = 850
Colour = 24
Case Else
End Select
Target.Interior.ColorIndex = Colour
End If

End Sub

Thanks in advance

--

Dave Peterson