| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting using VBA Case...
I have a need for conditional formatting based on greater than 6 conditions
and have found VBA programming on http://www.ozgrid.com/V (*thank you very much!*) and modified it slightly. It works perfectly for the data manually entered in the cells in the Target Range. However, I am trying to use the same conditional formatting on a calculated cell in the Target Range and I can't get it to work the way I want. The calculated cell will turn the color defined in the Case statement the first time it is calculated but then it stays that color and will not reflect the defined color for the the Case statement. The data in the Target Range are percentages and here is the modified code (row 16 cells are =Average(B3:B16), etc. and will not change color per the case statement) Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("B3:P16")) Is Nothing Then Select Case Target Case 0 To 0.0001 iscolor = 2 Case 0.0001 To 0.2499 icolor = 3 Case 0.25 To 0.4999 icolor = 7 Case 0.5 To 0.6499 icolor = 6 Case 0.65 To 0.7499 icolor = 8 Case 0.75 To 1# icolor = 4 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If End Sub Thanks, Tonly |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting using VBA Case...
Try the calculate event
Private Sub Worksheet_Calculate() Dim Target As Range Dim iColor As Integer For Each Target In Me.Range("B3:P16") Select Case Target Case 0 To 0.0001 iscolor = 2 Case 0.0001 To 0.2499 iColor = 3 Case 0.25 To 0.4999 iColor = 7 Case 0.5 To 0.6499 iColor = 6 Case 0.65 To 0.7499 iColor = 8 Case 0.75 To 1# iColor = 4 Case Else iColor = 2 End Select Target.Interior.ColorIndex = iColor End If End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Thyristor" <tknorris at plateautel dot net wrote in message ... I have a need for conditional formatting based on greater than 6 conditions and have found VBA programming on http://www.ozgrid.com/V (*thank you very much!*) and modified it slightly. It works perfectly for the data manually entered in the cells in the Target Range. However, I am trying to use the same conditional formatting on a calculated cell in the Target Range and I can't get it to work the way I want. The calculated cell will turn the color defined in the Case statement the first time it is calculated but then it stays that color and will not reflect the defined color for the the Case statement. The data in the Target Range are percentages and here is the modified code (row 16 cells are =Average(B3:B16), etc. and will not change color per the case statement) Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("B3:P16")) Is Nothing Then Select Case Target Case 0 To 0.0001 iscolor = 2 Case 0.0001 To 0.2499 icolor = 3 Case 0.25 To 0.4999 icolor = 7 Case 0.5 To 0.6499 icolor = 6 Case 0.65 To 0.7499 icolor = 8 Case 0.75 To 1# icolor = 4 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If End Sub Thanks, Tonly |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting using VBA Case...
Hi Tonly,
Try it as a Worksheet_Calculate event procedure... Private Sub Worksheet_Calculate() Dim icolor As Integer Dim rngCell For Each rngCell In Range("B3:P16") Select Case rngCell.Value Case 0 To 0.0001 icolor = 2 Case 0.0001 To 0.2499 icolor = 3 Case 0.25 To 0.4999 icolor = 7 Case 0.5 To 0.6499 icolor = 6 Case 0.65 To 0.7499 icolor = 8 Case 0.75 To 1# icolor = 4 Case Else icolor = 2 End Select rngCell.Interior.ColorIndex = icolor Next rngCell End Sub Also, you're 1st icolor, for Case 0 To 0.0001, had a typo (iscolor), and did you really mean =AVERAGE(B3:B16) in row 16 cells, which would create a circular reference? Ken Johnson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting using VBA Case...
Hi Thyristor,
It may be a coincidence, but looking at your code, your first case (0 to .0001) sets the value of the variable 'iscolor' where the rest are using tha variable 'icolor' - this may fix your problem. I didn't look any further than that. Cheers, Ivan. On Jan 10, 1:11 am, "Thyristor" <tknorris at plateautel dot net wrote: I have a need for conditional formatting based on greater than 6 conditions and have found VBA programming onhttp://www.ozgrid.com/V(*thank you very much!*) and modified it slightly. It works perfectly for the data manually entered in the cells in the Target Range. However, I am trying to use the same conditional formatting on a calculated cell in the Target Range and I can't get it to work the way I want. The calculated cell will turn the color defined in the Case statement the first time it is calculated but then it stays that color and will not reflect the defined color for the the Case statement. The data in the Target Range are percentages and here is the modified code (row 16 cells are =Average(B3:B16), etc. and will not change color per the case statement) Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("B3:P16")) Is Nothing Then Select Case Target Case 0 To 0.0001 iscolor = 2 Case 0.0001 To 0.2499 icolor = 3 Case 0.25 To 0.4999 icolor = 7 Case 0.5 To 0.6499 icolor = 6 Case 0.65 To 0.7499 icolor = 8 Case 0.75 To 1# icolor = 4 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If End Sub Thanks, Tonly |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting using VBA Case...
Thanks Bob, that did the job!
"Bob Phillips" wrote in message ... Try the calculate event Private Sub Worksheet_Calculate() Dim Target As Range Dim iColor As Integer For Each Target In Me.Range("B3:P16") Select Case Target Case 0 To 0.0001 iscolor = 2 Case 0.0001 To 0.2499 iColor = 3 Case 0.25 To 0.4999 iColor = 7 Case 0.5 To 0.6499 iColor = 6 Case 0.65 To 0.7499 iColor = 8 Case 0.75 To 1# iColor = 4 Case Else iColor = 2 End Select Target.Interior.ColorIndex = iColor End If End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Thyristor" <tknorris at plateautel dot net wrote in message ... I have a need for conditional formatting based on greater than 6 conditions and have found VBA programming on http://www.ozgrid.com/V (*thank you very much!*) and modified it slightly. It works perfectly for the data manually entered in the cells in the Target Range. However, I am trying to use the same conditional formatting on a calculated cell in the Target Range and I can't get it to work the way I want. The calculated cell will turn the color defined in the Case statement the first time it is calculated but then it stays that color and will not reflect the defined color for the the Case statement. The data in the Target Range are percentages and here is the modified code (row 16 cells are =Average(B3:B16), etc. and will not change color per the case statement) Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("B3:P16")) Is Nothing Then Select Case Target Case 0 To 0.0001 iscolor = 2 Case 0.0001 To 0.2499 icolor = 3 Case 0.25 To 0.4999 icolor = 7 Case 0.5 To 0.6499 icolor = 6 Case 0.65 To 0.7499 icolor = 8 Case 0.75 To 1# icolor = 4 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If End Sub Thanks, Tonly |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting - case sensitive text | Excel Worksheet Functions | |||
| Conditional Formatting based on Upper case | Excel Worksheet Functions | |||
| case sensitive conditional formatting | Excel Worksheet Functions | |||
| Select Case Conditional Formatting Sub? | Excel Discussion (Misc queries) | |||
| Select Case for Conditional Formatting | Excel Programming | |||
Linear Mode
