Alternate Address Group Shading in a Microsoft Access Report
Question details
Apply alternating background shades to address groups in an Access report so all residents at the same address share one shade.

- 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.
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.
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.
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.
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.
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.
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
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 individual resident rows instead of address groups
Use this simpler variation only when each detail row should alternate, regardless of its address.
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.

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.




