| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting for %
Wanting to make a cell change color if it hits one of these criteria:
between 1% and -1% then green between 1% and 5% then orange greater than 5% then purple between -1% and -5% then yellow less than -5% then red What can I do to the following script to make it work? Private Sub Worksheet_Change(ByVal Target As Range) Dim cel As Range Dim FormatRange As Range Set FormatRange = Range("A1:H875") If Not Application.Intersect(Target, FormatRange) Is Nothing Then For Each cel In FormatRange Select Case cel.Value Case Is <= 0.01, Is = -0.01 cel.Interior.ColorIndex = 4 Case Is <= 0.04999999999, Is = 0.01000000001 cel.Interior.ColorIndex = 27 Case Is = 0.05 cel.Interior.ColorIndex = 7 Case Is <= -0.01000000001, Is = -0.0499999999999 cel.Interior.ColorIndex = 44 Case Is <= -0.05 cel.Interior.ColorIndex = 3 Case Else cel.Interior.ColorIndex = xlColorIndexNone End Select Next cel End If End Sub Much appreciated, Bill |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting for %
In order for this to work, you have to change a field that's in the range you
call FormatRange. If the cells in FormatRange have calculations in them, you'll need to look at FormatRange.Precedents HTH, Barb Reinhardt "b1llt" wrote: Wanting to make a cell change color if it hits one of these criteria: between 1% and -1% then green between 1% and 5% then orange greater than 5% then purple between -1% and -5% then yellow less than -5% then red What can I do to the following script to make it work? Private Sub Worksheet_Change(ByVal Target As Range) Dim cel As Range Dim FormatRange As Range Set FormatRange = Range("A1:H875") If Not Application.Intersect(Target, FormatRange) Is Nothing Then For Each cel In FormatRange Select Case cel.Value Case Is <= 0.01, Is = -0.01 cel.Interior.ColorIndex = 4 Case Is <= 0.04999999999, Is = 0.01000000001 cel.Interior.ColorIndex = 27 Case Is = 0.05 cel.Interior.ColorIndex = 7 Case Is <= -0.01000000001, Is = -0.0499999999999 cel.Interior.ColorIndex = 44 Case Is <= -0.05 cel.Interior.ColorIndex = 3 Case Else cel.Interior.ColorIndex = xlColorIndexNone End Select Next cel End If End Sub Much appreciated, Bill |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
