Link to home
Create AccountLog in
Avatar of David Bigelow
David BigelowFlag for United States of America

asked on

How do I change a report line color based on a field value, with VBA.

In Access 2007, I have a report, rptWorkarounds, with the field, Workaround_Status, that is filled with Open or Resolved. I'd like the line of the report to change to a gray background when I change the status to Resolved. So far, I have this, but it's not working.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Workaround_Status = "Resolved" Then
     Me.Section(acDetail).BackColor = 12632256 'grey
     Me.Section(acDetail).BackColor = 16777215 ' std white
 End If
End Sub
Avatar of Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Flag of United States of America image

Your code should may work only in print preview not report view.  What mode are you viewing?

If your code does not work in Print Preview try:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Workaround_Status = "Resolved" Then
     Me.Detail.BackColor = 12632256 'grey
     Me.Detail.BackColor = 16777215 ' std white
 End If
End Sub 

Open in new window

Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I would agree with Jeff that conditional formatting is the "preferred" method for accomplishing this task.  However, there are limits to the number of conditional formats you can apply.

The advantage of Boyd's technique is that you can have as many different conditions and colors as you want, although going beyond the default + 3 will generally cause more confusion than it is worth (unless you include a key somewhere in the form or report to indicate what each color code means).
To be clear, the previous experts have answered your post as written.
So take my post as just an FYI.

Yes, ...there are advantages and disadvantages to both approaches

To be toatly honest, I too, mostly will go with VBA because:
I am familiar with the code syntax
I always use Print Preview for reports
You can do "crazier" things with VBA for example: (make gray if the second field is between 1 and 7, ...and the fourth field is Monday, and the state is NY, Or the department is 7)
You can have error handling and comments in VBA
Compatibility with older versions of Access where conditional formatting did not exist.

But for simple things like: Make a line Grey, I would investigate conditional formatting first.

But again, ...if you need a solution for VBA, then what the previous tow experts have posted is the way to go...


Avatar of David Bigelow


Is there a way to view a Conditional Report Manager in Access 2007 to delete previously entered conditional formats (not VBA)? I'm thinking they may be interfering with my current testing.

sorry for causing any confusion here...

Open the report in design view.

1. You can select all the texboxes (in the detail section), then click: Format-->Control Format-->Conditional formatting
Then in the dialog box, will display "Multiple"
From that drop down, can select each field and see the conditional formatting associated with it...

Or you can simply select each control individually, then click: Format-->Control Format-->Conditional formatting
But there, you might forget to select a control that might have formatting set...
I'm sorry; I'm completely lost. I've seen several posts with Form-->Control Format-->Conditional Formatting while looking for answers, but I'm not finding that option. Are your instructions for Access 2007? In the ribbon, Design tab, Font group, I have a Conditional icon that I've been using, but it doesn't have a Conditional Report Manager associated with it, just the new rule window. There's a Format tab in the Property Sheet, but it has no Conditional Formatting option.

Anywhere else I should be looking? Maybe you could post a screen shot. It may be completely in front of my nose, but I'm not seeing it.

I've attached a screen shot of what I've been using.

Access 2007:

User generated image
I finally found what was causing the trouble. In using an Access color theme, the BackStyle was transparent. Hence, any BackColor controls I put in were overridden by the default transparent setting. I found that here: Trying to block highlight the controls and change the BackStyle setting to Normal did not work. I had to select them one at a time to change the BackStyle to Normal.

And Access 2007 doesn't have a multiple objects selection field in the Conditional Formatting window. The closest it comes to any sort multiple indication is having garbled fields in the Conditional Formatting window when selecting a row of objects after setting a condition on one of the objects inside the row.

This didn't highlight the row, but it highlighted all of the fields in the row, which is close enough. And the user was ecstatic that I grayed the font somewhat to make the row a slight struggle to read. We don't want people using workarounds for issues that have been resolved.

After having spend probably 12 hours on this issue, I didn't want to spend any more time testing the VBA.
Now that you mention it, ...I do remember this happening a while back...

I thought MS would have fixed this in a hotfix or something....