Excel VBA - Trying to change color of cell conditionally

[abrahamvionas]

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!

 

[Bob Phillips]

Abraham,

The VB code, goes in the worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Not Intersect(Target, Range("C4:K4,C9:
K9,I14:K14,C20:K20,C25:K25,H30:K30")) Is Nothing Then
Select Case Target.Value
Case Is > 0.1: Target.Interior.ColorIndex = 36
Case Is <= 0.1: Target.Interior.ColorIndex = 34
End Select
End If

ws_exit:

Application.EnableEvents = True
End Sub


The conditional formatting problem sounds to me that used absolute
references to me. I think that all your formulae check against $G$5, so they
all reflect that cell's value. What you should do is select all these cells,
and put the first cell in the formula (C4 in this case).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[abrahamvionas]

Thanks for the solution you provided. I added the code to Module1 an
removed everything else (I assume this would be correct). However
while it doesn't create any errors, it also doesn't seem to be doin
anything, and running it doesn't yield a response either... Also
changing percentage values on the spreadsheet don't cause the effect
I'd like to get from the code.

I forgot on my initial post to include that I currently have Iteration
(of 2) turned on in the sheet in order to use what would otherwise b
circular reference errors.

I've realized that regardless of conditional formatting if I enter
value in say, C4 which causes a change in the percentage valu
displayed in C5, and then move over to D4 and enter a new value, th
moment I press enter the percentage value displaying in C5 disappears
This completely mystifies me. My only thought is that perhaps th
Iterations rule kicks in whenever a circular reference would occur
which in practice is every time a value is changed in a cell such as C
or D4, etc... And that when it kicks in it nullifies or undoes whateve
it did before...

Any more ideas? Do I need to take out the iterations, and if so how d
I solve for circular references. The following are the formulas fo
cells C4, C5, C6, and C7 to illustrate the mechanics I'm talkin
about:

In C4 (there's nothing, other than a user entered value, such a
24.50)

In C5:
=IF(C4<>C6,((C4-C6)/C6),"")

In C6:
=IF(C6<>C4, IF(C7=C6,C4,C6), C6)

In C7:
=IF(C4<>C6,C6,C7)

Essentially C7 stores the value previously in C4 (before a new value i
entered into C4) in order so that I can determine the percentage o
difference between the old value and the new value.

Thanks again

 

[Bob Phillips]

Thanks for the solution you provided. I added the code to Module1 and
removed everything else (I assume this would be correct). However,
while it doesn't create any errors, it also doesn't seem to be doing
anything, and running it doesn't yield a response either... Also,
changing percentage values on the spreadsheet don't cause the effects
I'd like to get from the code.

Abraham,

It doesn't go in Module 1, it goes in the worksheet code module. To get
here, right-click the sheet name tab, Select View Code from the menu, and
paste the code in there.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[abrahamvionas]

I know I'm frustrating because I'm such a newbie with VB, sorry about
that. Thanks for your insightful assistance though.

I suspect that the Iterations I have turned on for the worksheet are
causing problems for the VB script. I suspect this because each time I
move on to the next cell to put in a different value the percentage
cell associated with the last cell I changed resets (i.e. goes back to
containing NO value).

However, without the Iterations being turned on my formulas don't work
because they'd create circular references. And yet I don't have a clue
about how to do the same thing I've managed to do with Iterations and
Formulas in Excel, in VB.

The psuedo code would go something like this:

Upon a change in the value of AnyCell in Range("C4:K4","C9:K9")
Do:
Store previous value into X
Divide value in X by new Value
Display resulting percentage in CellChanged.Offset(1,0) -- i.e.
right below the cell in which a change occured.

Mostly the part I can't think of a way to do is making it be triggered
by a change to a cells value, and then recognizing which cell was
changed, and storing data in appropriate variables (which have an
established association with the variable which has changed) - say the
initial change occurs in C4, the procedure would need to identify that
fact, and then store the following values in C6 and C7, and display the
resulting percentage in C5.

Again, sorry to be such a pain, I really appreciate your help!

 

[Bob Phillips]

Abraham,

Why don't you post me the workbook. Post it to

bob . phillips @ tiscali . co . uk

remove the embedded spaces.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)