conditional formatting with calculated colors

[Guest]

I am trying to set the background color of a text box with conditional
formatting.

I tried conditional formatting from the format menu but three colors isn't
enough.

I tried assigning a color coding function to the BackColor property but the
builder only accepts colors from the color chart and rejects all other
functions.

Same holds for the conditional formatting color.

I also tried setting the BackColor property in the BeforeUpdate and
AfterUpdate event procedures with no success.

Clippy says "You can set conditional formatting based on the value in a
control, an arbitrary expression referencing another control, a user-defined
Visual Basic for Applications function, or the control with focus. You can
change the color of the text, make the text bold, italic, or underlined, or
enable or disable a control when it meets or doesn't meet criteria you
specify."

Just how do you do that with a user-defined Visual Basic for Applications
function ? I'm tired of wasting my time on trial and error.

Does anyone know which event procedure will apply conditional formatting ?

I would appreciate an example assigning the following function's result to
the BackColor property

Public Function ColorCode(A as variant) As LongInteger

Thanks in advance

 

[Allen Browne]

Open the form in design view.

Open the Immediate Window (Ctrl+G).

Enter something like this:
Forms!Form1!Text0.FormatConditions(0).BackColor = RGB(255,96,96)

Use your own form and text box names, and the RGB value you want. If you
have multiple FormatConditions, use 0, 1, or 2 as appropriate.

This assumes the Format Condition already exisits, and you ave just changing
the color.