Custom colors for conditional formatting

[btj]

I am trying to figure out a way that I can add custom colors when I use conditional formatting.

Does anyone know how/if this can be done?

Thanks,
Ben

 

[hsitz]

You can make use of a control's '.FormatConditions' property to change anything you want about conditional formatting.

For example, if you have a formatting condition defined, you can put this in the 'on load' event of a form to change the color that will be used:

Me.TextBox3.FormatConditions(0).ForeColor = 8453888

You can also programmatically add, delete, or modify formatconditions. You will want to check out the VBA help on the formatcondition property to figure out how to do the things you want. -- Herb

 

[RickSpr]

You can't do it via the user interface, but you can do it in VBA code.

The text box or combo box control has a FormatConditions property, which returns a reference to a collection of FormatCondition objects. Each FormatCondition object has BackColor and ForeColor properties you can set to any color you like.

Assuming you want to save the form with the custom colors set, you'll need to write a routine that you can run at design time, from the Immediate Window. The form must be open in design view when you run the procedure. After you run it (once for each condition, for each control), save the form. You could then delete the procedure, since the colors have been saved with the form design, though you might want to keep it around for reuse later.

Here's a simple procedure that should do the job. (I haven't tested it, though.)

Public Sub SetFCColor(FormName As String, _
                      ControlName As String, _
                      Condition As Integer, _
                      BackColor As Integer, _
                      Optional ForeColor As Integer = 0)
    Dim ctl As Control
    Dim fmc As FormatCondition
    
    Set ctl = Forms(FormName).Controls(ControlName)
    Select Case ctl.ControlType
        Case acComboBox, acTextBox
            Set fmc = ctl.FormatConditions(Condition)
            fmc.BackColor = BackColor
            fmc.ForeColor = ForeColor
        Case Else
            MsgBox ControlName & " is not a text box or combo box"
    End Select
End Sub

Copy this code and paste it in a new module. Then open your form in design mode, add the conditions (with any old colors) if you haven't already, and switch to the Immediate Window. For each condition on each control that you want to change, enter the following:
SetFCColor <name of form>, <name of control>, <condition number>, <back color>, <fore color>

The condition number 0 refers to the default colors. Conditions 1 through n refer to the first, second, etc. conditions you have entered.

For the back color and fore color values, you can either specify the numerical value, or you can enter the RGB() function that computes the numerical value from the red, green, and blue components, each of which is a level from 0 to 255. To get a numerical value for a color, you might set the plain old Back Color property of a form using the Build button, and then copy the number that pops up in the property sheet to use in the Immediate Window.

You can omit the fore color when you issue the SetFCColor calls in the Immediate Window; if you do, the color of the text will be set to 0 (black). Rick Sprague