Conditional Format With VBA

[Jason]

I cant seem to get this to work! Basically, what I want to do is
format some text boxes bases on the following:

0 to -4.99% = Green

-5.00 to -7.99 = Yellow
< -8.00 = Red

I have used the following code:

If Me.txtDivGL > 0 Or Me.txtDivGL < -5 Then
Me.txtDivGL.Bac kColor = vbGreen
End If
If Me.txtDivGL < -5 Or Me.txtDivGL > -8 Then
Me.txtDivGL.Bac kColor = vbYellow
End If
If Me.txtDivGL < -8 Then
Me.txtDivGL.Bac kColor = vbRed
End If

Why doesn't this work? It always comes up with the wrong color. The
value of the text box is based on a lookup from a table and then
formatted as a percent.

Please help as I am clueless as to why this won't work.

[pietlinden]

Hmm... this is an odd way of expressing your rule. That's probably why
it won't work.

How about using a case statement? the problem is that your ranges
overlap. Express the rule this way:

From <start value> to <stop value>

Color = <some color>

I'm not even going to try to figure out what your code is supposed to
do. What I'm getting at is this - express it in plain English first -
for yourself as much as people trying to help you. Once you do that,
coding it is pretty simple.

or look up Select Case in the VBA help

[Randy Harris]

"Jason" <ja*****@sw.rr. com> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.com...

I cant seem to get this to work! Basically, what I want to do is
format some text boxes bases on the following:

0 to -4.99% = Green

-5.00 to -7.99 = Yellow
< -8.00 = Red

I have used the following code:

If Me.txtDivGL > 0 Or Me.txtDivGL < -5 Then
Me.txtDivGL.Bac kColor = vbGreen
End If
If Me.txtDivGL < -5 Or Me.txtDivGL > -8 Then
Me.txtDivGL.Bac kColor = vbYellow
End If
If Me.txtDivGL < -8 Then
Me.txtDivGL.Bac kColor = vbRed
End If

Why doesn't this work? It always comes up with the wrong color. The
value of the text box is based on a lookup from a table and then
formatted as a percent.

Please help as I am clueless as to why this won't work.

With that code it looks to me as though it would never be green. If the
value is -8 or greater it would be yellow, less than -9 red.

How about something like:

Select Case Me.txtDivGL
Case 0 to 4.99
Me.txtDivGL.Bac kColor = vbGreen
Case -5 to -7.99
Me.txtDivGL.Bac kColor = vbYellow
Case < -8
Me.txtDivGL.Bac kColor = vbRed
Case Else
Me.txtDivGL.Bac kColor = somerrorcolor
End Select