| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting in VBA not working
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting in VBA not working
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting in VBA not working
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting in VBA not working
(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 Marilyn wrote: 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" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting ROW not working | Excel Discussion (Misc queries) | |||
| Conditional Formatting Not Working In Macro | Excel Discussion (Misc queries) | |||
| IF function not working in conditional formatting | Excel Worksheet Functions | |||
| Conditional Formatting is not working... | Excel Worksheet Functions | |||
| conditional formatting not working in every cell | Excel Discussion (Misc queries) | |||
Linear Mode
