I'm getting frustrated with this problem because I can't seem to find
any other questions that are similar enough to help me figure out what
I need to do to get my VB to work.

Essentially, I have a spreadsheet with (6) different ranges(C4:K4,
C9:K9, I14:K14, C20:K20, C25:K25, H30:K30). I want to change the color
of cells in those ranges depending on the percentage value in the cell
- which is the result of a formula operation already occupying the cell
[=IF(K4<K6,((K4/K6)-1),"")].

If the percentage value is .10 then I want the color of the cell to
be changed to Index 36 (pastel red), and if the percentage value is <
-.10 then I want to change the cell color to Index 34 (pastel green).

I attempted to use conditional formatting to solve my problem, and it
appeared to work - halfway - in that it would work until a new
percentage value registered in another conditionally formatted cell.
I.e. the cell C5 would be changed to green, until G5's percentage value
changed, at which time C5 would revert to a default color of pastel
red, and G5 would become conditionally formatted (taking on whatever
color was defined).

My need is two-fold. I'd like to know what the heck was up with the
conditional formatting, and why it was being so funky; and secondly I
need to know what VB I need to use to change cell colors based upon
criteria in those six ranges I specified. Any assistance is very much
appreciated!

Currently I'm stuck with the following code:

(attached to "Sheet1")
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, ActiveSheet.Range() Is Nothing Then
Call Test
End If
End Sub

(in Module1)
Sub Main_NvsInstanceHook()
'
' Main_NvsInstanceHook
' This routine calls the appropriate InstanceHook routines
'

Application.Run "NVSUSER.XLM!YTDDrill"

End Sub

Sub Test()

Dim oCell, r1, r2, r3, r4, r5, r6, MyRange As Range

For Each oCell In Range("C5:K5", "C10:K10")
If oCell.Value < "" Then
Select Case oCell.Value
Case Is < "-0.1"
oCell.Interior.ColorIndex = 34
Case Is "0.1"
oCell.Interior.ColorIndex = 36
Case expr1 To expr2
oCell.Interior.ColorIndex = 40
End Select
End If
Next oCell

End Sub


Thanks for you help in advance!


---
Message posted from http://www.ExcelForum.com/