Conditional formatting is a great feature introduced in Access 2000, it allows you to apply different formats to records on a form based on values and formulas you setup. In this example, I’m going to build on an article by Microsoft you can find here.

Apply conditional formatting using code

Colors are better for some…

My client wanted to use colors to quickly see the status of each item, (I would have normally used a drop down box for easy sorting on status, both colors and drop downs have their benefits and drawbacks). I added buttons to each record so that the customer can click on each to change the status as shown below:

Apply conditional formatting using code

Each button assigns a numeric value to the GraphicStatusID field and saves the record so that the user can immediately see the change on the form. Here’s the button code:

Private Sub cmdApproved_Click()
Me.GraphicStatusID = conApprvdGreen
Me.DateGraphicApproved = Date
DoCmd.RunCommand acCmdSaveRecord
End Sub

Here’s how it looks:

Apply conditional formatting using code

Whole Row Formatting

(Notice there are five buttons above, one color per button. I overcame that obstacle by not using the whole row, read more below.)

Use the Controls Collection to apply formatting

There are many reasons to use code instead of Access’ interface to add formatting conditions, mainly it’s a lot easier to apply the formatting when you have to do many controls on your form. Having it all in one place will make it easy to update the formulas and formating too.

The first step is to use the Tag property to identify which controls on your form will be processed by your code, in this case I used “Conditional”. I then used the code posted here and called the ApplyCondFormatting subroutine on the form’s Load event.

Apply conditional formatting using code

Tag Property

The form has a Controls collection that you can iterate using the For Each method and a control variable, (ctl). Looping through all of the controls allows you to check the Tag property and if there is a match, apply the formating code. The code works if you have only three conditions.

But what if you need to apply more than three formats to a row?

There is no way around the three formatting limit so I decided to take a hybrid approach: Use the first two columns to indicate a color status instead of the entire row. The first column, Vendor, has  three and the PO column has two more for a total of five status colors. When the user clicks on any of the status buttons the color will now move between the first and second column as shown below:Apply conditional formatting using code

You can see the code for staggered formating here.

This article illustrates the power of the control collection and applying conditional formatting in your code.