Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

Change conditional format color VBA

How can I change the color of conditional row highlight using VBA on run time?

I am using access 2007. As shown in the attached image, the condition logic is taken care of and it works fine. I want to make applying user preferred color also possible.

Thank you.
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Unfortunately, not in A2003.
But in A2010 - the 3 limit has been lifted.
Avatar of Mike Eghtebas


I am using 2007
I think ... the > 3 limit started in A2007 (I never used A2007)
I am not worried about number of conditions (at least not now).

Using your link, I want to supply:

i= AddFormats ("ControlName", Me, intColor)

But not sure how to change the following function to do the job:

There is one condition and I only need to change background color of the control.

Can you modify the attached code from your link to do the job?

Thank you,


Function AddFormats(ctlSource As Control, frm As Form) As Integer

    Dim ctl As Control
    Dim fcdSource As FormatCondition
    Dim fcdDestination As FormatCondition
    Dim varOperator As Variant
    Dim varType As Variant
    Dim varExpression1 As Variant
    Dim varExpression2 As Variant
    'Dim intConditionCount As Integer
    Dim intCount As Integer
    'intConditionCount = ctlSource.FormatConditions.Count
    'For Each ctl In frm.Controls
        If = Then
            ' This is the source.  Don't apply formatting.
        ElseIf ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            'intCount = 0
            ' Bulk remove all current FormatConditions
            'Do Until intCount = intConditionCount
                Set fcdSource = ctlSource.FormatConditions.Item(intCount)
                varOperator = fcdSource.Operator
                varType = fcdSource.Type
                varExpression1 = fcdSource.Expression1
                varExpression2 = fcdSource.Expression2
                ' Add the FormatCondition
                'ctl.FormatConditions.Add varType, varOperator, varExpression1, varExpression2
                ' Reference the FormatCondition to apply formatting.
                ' Note: The FormatCondition cannot be referenced
                ' in this manner until it exists.
                Set fcdDestination = ctl.FormatConditions.Item(intCount)
                With fcdDestination
                    .BackColor = fcdSource.BackColor
                    .FontBold = fcdSource.FontBold
                    .FontItalic = fcdSource.FontItalic
                    .FontUnderline = fcdSource.FontUnderline
                    .ForeColor = fcdSource.ForeColor
                End With
                ' Move to the next FormatCondition
                'intCount = intCount + 1
        End If
    'Next ctl
    ' Cleanup
    'AddFormats = intConditionCount
    MsgBox "There were " & AddFormats & " Conditional Format(s) applied to all text and combo boxes except the source."
    Set ctl = Nothing
    Set fcdSource = Nothing
    Set fcdDestination = Nothing
    Set varOperator = Nothing
    Set varType = Nothing
    Set varExpression1 = Nothing
    Set varExpression2 = Nothing
    'intConditionCount = 0
    intCount = 0

End Function