Formatting Issues

Alternate Address Group Shading in a Microsoft Access Report

Khadija KhanKhadija Khan Nov 13, 2026 868 views

Question details

Apply alternating background shades to address groups in an Access report so all residents at the same address share one shade.

Alternate Address Group Shading in a Microsoft Access Report
Product
Microsoft Access
Device & OS
not provided
Scenario
A report lists residents grouped by address, and alternating group backgrounds should make adjacent addresses easier to distinguish.
Observed behavior
The desired result is a consistent background within each address group, alternating between white and light gray at each new address.
Before you start

Open the report in Design View and verify which field or combination of fields uniquely identifies an address. Use Print Preview to check the result because the Detail section's Format event runs during report formatting for preview or printing, not in Report View.

Solution 1Recommended

Alternate shading once per address group

Use this method when several residents share an address and every resident in that group should have the same background.

A running-sum text box in the Detail section increments for every resident, which alternates individual rows. To alternate entire address groups, place the running-sum control in the address group header instead.

1
Create an address group

Open the report in Design View. On the Design tab, select Group & Sort, then Add a group and choose the address field or a field that uniquely identifies the complete address. Expand More and enable a group header.

2
Add the group counter

Insert a text box in the address group header. In its Property Sheet, set Name to txtRunSum, Control Source to =1, Running Sum to Over All, and Visible to No. This counter increments once for each address group.

3
Make detail controls transparent

Select the text boxes in the Detail section and set Property Sheet > Format > Back Style to Transparent. Apply the same setting to any labels whose backgrounds should reveal the section shading. If the Detail section has an Alternate Back Color property, set it to No Color to prevent built-in row shading from interfering.

4
Add the Detail Format event

Select the Detail section, open Property Sheet > Event > On Format, choose [Event Procedure], and click the ellipsis button. In the VBA editor, use: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Nz(Me!txtRunSum, 0) Mod 2 = 0 Then Me.Section(acDetail).BackColor = RGB(244, 244, 244) Else Me.Section(acDetail).BackColor = RGB(255, 255, 255) End If End Sub

5
Preview and adjust the shade

Open Print Preview and compare two adjacent address groups. All residents at one address should share a color. Change RGB(244, 244, 244) to a darker or lighter gray if needed, then save the report.

Alternate shading once per address group
Use white for the unshaded background: The VBA BackColor property expects a color value, so use RGB(255, 255, 255) for white. No Color is appropriate for the separate Alternate Back Color setting, not as a literal value in this VBA code.
Free Microsoft Office alternative

Use WPS Office for Spreadsheet-Based Resident Lists

WPS Office is a free, lightweight, easy-to-use alternative for spreadsheet-based resident lists and related documents. Its familiar interface and Microsoft Office format compatibility help you migrate those files smoothly. It does not replace Microsoft Access or run Access report events, so keep the database report and this VBA fix in Access.

Organize exported resident lists in WPS Spreadsheet for review and sharing.Supports Microsoft Excel .xlsx and .xls files for spreadsheet-based workflows.Supports Microsoft Word .docx and PowerPoint .pptx files for accompanying documents and presentations.A familiar interface and lightweight suite simplify migration of supported Office files.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the shading change for every resident instead of every address?

A text box with Control Source =1 and Running Sum set to Over All increments each time its section is processed. Move txtRunSum from the Detail section to the address group header so it increments once per address group.

Why do the text boxes stay white over a gray detail background?

Their backgrounds may be opaque. In Design View, select each affected text box and set Property Sheet > Format > Back Style to Transparent so the Detail section's BackColor shows through.

Why can't I see the VBA shading in Report View?

The Detail section's Format event does not run in Report View. Open Print Preview or print the report to evaluate the event-driven shading.

How can I make the alternating gray darker?

Open the Detail section's On Format event procedure and replace RGB(244, 244, 244) with a darker gray, such as RGB(225, 225, 225). Use matching red, green, and blue values for a neutral gray.

Can WPS Office run this Access report or its VBA event?

No. WPS Office does not run Microsoft Access database reports or their event procedures. Apply this fix in Access; for a spreadsheet-based workflow, export the resident data to an Excel-compatible file and open it in WPS Spreadsheet.