Conditional formatting of subform cells 2

[HowardB]

Hi All,

Can anyone provide me with some example code of how to achieve conditional formatting of individual cell display properties within a subform based on values displayed in the subform cells. The traditional conditional formatting only allows 3 conditions (via Formatting menu\Conditional Formatting in Form design mode)and I want to use about 5 conditions with a SELECT CASE type control structure e.g.

If the value in the subform control (textbox) is less than 60 then make the background color blue, if the value is between 60 and 80 then make the background cell color green etc. The subform and all the controls are bound to an underlying table. I am also not 100% sure where to execute the code my current thinking is either attached to the OnOpen or OnLoad events. Hope someone can help,

regards HowardB

 

[HowardB]

So do I take it nobody has a clue about this issue?

 

[trevorwilliams2]

This can be done, I've seen it, and I am looking for a sample of the code.

If you've had any luck finding this, let me know.

 

[stephenk1973]

On the 'current event' of the subform.....where CurrentQty is the field name

If CurrentQty = 0 Then
CurrentQty.BackColor = vbWhite
ElseIf CurrentQty > 0 And CurrentQty < 50 Then
CurrentQty.BackColor = vbBlue
ElseIf CurrentQty > 50 And CurrentQty < 100 Then
CurrentQty.BackColor = vbGreen
ElseIf CurrentQty > 100 Then
CurrentQty.BackColor = vbRed
End If

This will not work on continuous. For continuous forms the code is applied to all the records not just the one that has focus, hence everything turns green, blue, red what ever.

Hope this helps

Stephen

 

[trevorwilliams2]

Thanks for your reply.

I actually found some really good examples of conditional formatting on a continuous form at

http://www.lebans.com/conditionalformatting.htm.

Download the zipped database at the top of the page.