Conditional Format With VBA

I cant seem to get this to work! Basically, what I want to do is
format some text boxes bases on the following:
[color=blue]
>0 to -4.99% = Green[/color]
-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.BackColor = vbGreen
End If
If Me.txtDivGL < -5 Or Me.txtDivGL > -8 Then
Me.txtDivGL.BackColor = vbYellow
End If
If Me.txtDivGL < -8 Then
Me.txtDivGL.BackColor = 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.

re: Conditional Format With VBA

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:
[color=blue]
>From <start value> to <stop value>[/color]
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

re: Conditional Format With VBA

"Jason" <jasmith@sw.rr.com> wrote in message
news:1126820201.476270.53810@g43g2000cwa.googlegro ups.com...[color=blue]
> I cant seem to get this to work! Basically, what I want to do is
> format some text boxes bases on the following:
>[color=green]
> >0 to -4.99% = Green[/color]
> -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.BackColor = vbGreen
> End If
> If Me.txtDivGL < -5 Or Me.txtDivGL > -8 Then
> Me.txtDivGL.BackColor = vbYellow
> End If
> If Me.txtDivGL < -8 Then
> Me.txtDivGL.BackColor = 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.[/color]

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.BackColor = vbGreen
Case -5 to -7.99
Me.txtDivGL.BackColor = vbYellow
Case < -8
Me.txtDivGL.BackColor = vbRed
Case Else
Me.txtDivGL.BackColor = somerrorcolor
End Select