Conditional formatting in VBA not working

[Marilyn]

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]

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]

Thanks Dave
your question " what happens if the target .value is 499?"
hmmm. this is what I want
if value is over 850 = color 24
if value is between 700 and 849 another color
if value equals between 500 and 699 another color
if value equals 101 and 499 another color
if the value is less than 100 another color
if the cell is blank no color
I printed the color index values from the patternColor Index Property in VBA
but the numbers on the excel sheet do not match the color on the chart.
Where can I find the color index value. Thank you in advance

 

[Dave Peterson]

(I think...)

Select Case Target.value
Case Is >= 850
Colour = 24
Case Is >= 700
Colour = 50
Case Is >= 500
Colour = 8
Case Is >= 100
Colour = 6
case else
colour = xlnone 'is xlnone correct for what you want?
End Select

Just keep adding criteria in a nice order if you need more.

If I know the colors that I want, I'll record a macro in a separate (temporary)
workbook when I change to that color. Then I'll just steal that number from the
recorded macro.

David McRitchie has lots of notes (for xl2003 and below):
http://mvps.org/dmcritchie/excel/colors.htm