VBA & Conditional Formating (1 Viewer)

I am trying to change the font color in a cell using A97 using VBA (I suck at VBA but am gonna learn if it kills me).
Code:
Private Sub Text3_Change()
If Me.Text3 < 0 Then
Me.Text3.ForeColor = vbRed
Else
Me.Text3.ForeColor = vbBlack
End If
End Sub

Form fields:
1stNum
2ndNum
Text3 (1stnum - 2ndnum)

When text3 is a Neg value I would like it in red.
Also, I am not sure which event to tie this to.."change" seemed cool.

Try the On Current event.

If this is a continuous form, changing the Text3 fore color will change it in ALL records which I'm sure is not what you want. And from what I understand, A97 does not have the conditional formatting option.

Yeah, I know 97 doesn't have that feature but I'm think I can do it with VBA cant't I?

I tried "on current"...but I think your right.
I can change the text3 to red with my code but it doesn't change to black on numbers greater than zero.

Got it!
I found I needed to refresh the form.

Thanks!

Thought I had it but didn't..

The following code works and is attached to "on current" event
but the problem is when I change the data in the 1st or/2nd fields I do not see the color change until I either close the form and come back in or go to another record and come back.
Which means I am missing something...I have tried refreshing, requery and refrasing my cuss words on the form.

Private Sub Form_Current()
If Me.Text3 < 0 Then
Me.Text3.ForeColor = vbRed
Else
Me.Text3.ForeColor = vbBlack
End If

Me.Refresh

End Sub


Thanks