VBA Conditional Formatting Problem 1

[sirkenj]

Hello again! Once again, I'm stumped! I'm trying to code a form to do conditional formatting based on the value of a text box. My text box is txtSrat, whose value is derived from a function. The function will return either "S1", "S2", "S3", or "S4". I want to highlight each line differently based on the "S*" value. I am currently trying to place the code in my forms OnCurrent event, and will later add it also to the OnChange event. Right now, when I execute the code below, all lines get highligted as if they were "S1" regardless of the value in txtSrat. Your help is greatly appreciated!

Private Sub Form_Current()
Dim intColor As Single

Select Case Me![txtSrat]
Case Is = "S1"
intColor = 13434828
Case Is = "S2"
intColor = 10092543
Case Is = "S3"
intColor = 10079487
Case Is = "S4"
intColor = 13408767
End Select

Me![LIN].BackColor = intColor
Me![SubLIN].BackColor = intColor
Me![Nomen].BackColor = intColor
Me![Rqd].BackColor = intColor
Me![Auth].BackColor = intColor
Me![O/H].BackColor = intColor
Me![PctgFill].BackColor = intColor
Me![txtSrat].BackColor = intColor
Me![Remarks].BackColor = intColor
Me![D/I].BackColor = intColor
Me![ERC].BackColor = intColor
Me![RptDate].BackColor = intColor
Me![UIC].BackColor = intColor


End Sub

 

[PHV]

I guess you're in a continuous form with an unbound textbox.
If ac2k or above take a look at Conditional format in the the Format menu.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sirkenj]

I am using continuous forms, and have looked at conditional formatting. I only posted "part" of the problem...I realize if I'm dealing with only 4 possible variables in txtSrat, I can use conditional formatting. The problem is that there needs to be an If..Then statement before the conditional formatting starts which looks at the value of a separate text box (txtExempt). If txtExempt is True, then another conditional format needs to take place.

I was hoping to not complicate the matter by posting the first part of my problem...I figured if someone could point me in the right direction on getting the conditional formatting working through VBA, I could figure the rest out with the if...then statement.