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.
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:
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
Here’s how it looks:
(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.
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:
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.
Access 2010, I have this report with 11 columns and two of them are date formats (Start and End) date. Please show me how to use Conditional Formatting on a report change font color to green on that row if between start and end date are true with current date.
Thank you in advance!
Can any help with Conditional Formatting. i am using three columns. for example if qty in first column is equal to 2nd column then the third column shows clear, but if qty is not equal in first and second column, then third column should shows Pending.
i am not a developer. Kindly help me, how can i use formatting to get result in third column.
Color and display attributes are an important feature of my existing Pawnshop Management application. It helps my users to quickly identify key information, such as: the number of days a pawn has been active is displayed in blinking red color if it’s past due, negative money amounts, customer status and other conditional situations.
“There is no way around the three formatting limit”.
You can always add more formatting in VBA.
Great help, however I must be either slow or stupid. I need to just have simple logic in a data sheet view to take an amount and have logic to say if > 100 then make the control red, > 200 yellow , or > 300 green . I did this logic in VB for a standard form this way, on a data sheet lost how to do this.
Private Sub Form_Current()
If Process_days < 100 Then
Process_days.BackColor = vbYellow
I can't use the "easy" conditional formatting that access since this kills performance. We are running access using a SQL data base on the server. When I setup the access conditional formatting you could literally watch the screen get "loaded". One I removed the conditional formatting, not an issue. So I guess VB is the way to do it.
Good to hear! I don’t develop in 2010 since there are some serious issues with backwards compatibility with Access 2007. Still, I would recommend the coding approach instead of manually trying to manage conditions on a form, it’s much easier to update in one spot on your code then on all of the controls.
Thanks for your comment!
“There is no way around the three formatting limit”. Access 2010 allows up to 50 concurrent format conditions so I guess now maybe there is…