Formatting Issues

Fix Microsoft Access Conditional Formatting That Does Not Change Text Color

Algirdas JasaitisAlgirdas Jasaitis Nov 22, 2026 868 views

Question details

The user needs an Access form to change blood pressure text colors based on target ranges stored in a profile table.

Fix Microsoft Access Conditional Formatting That Does Not Change Text Color
Product
Microsoft Access
Device & OS
not provided
Scenario
A form uses conditional formatting expressions and DLookup to compare blood pressure readings with profile-specific lower and upper targets.
Observed behavior
The text remains black instead of changing color according to the target range.
Before you start

Check the actual field names, table name, and profile identifier in your database. Confirm whether the profile table contains one record or multiple users, because DLookup needs criteria to select the correct user when there are multiple records.

Solution 1Recommended

Correct the field types and conditional formatting rules

Use this approach when a blood pressure control stays black because its values or lookup targets are not being compared as numbers.

The example rules below assume that My Profile contains a single profile record and that its target fields contain numbers. If the table contains multiple profiles, add criteria to each DLookup expression so it returns the current user's targets.

1
Check the reading field

Open the table containing Systolic in Design View and check its Data Type. Use Number with an appropriate Field Size, such as Integer, for whole-number readings; check existing text values for nonnumeric entries before changing the type.

2
Check missing values and targets

In table Design View, remove the default value of 0 from fields where zero means no reading, and allow Null instead. Check that TargetLoSystolic and TargetHiSystolic in the My Profile table are numeric and contain values for the relevant profile.

3
Test the lookup

In form Design View, add a temporary unbound text box and set its Control Source to =DLookup("TargetLoSystolic","My Profile"). Switch to Form View and verify that it displays the expected numeric target. Repeat with TargetHiSystolic.

4
Add profile criteria if needed

If My Profile has multiple records, include a criteria argument in each lookup. For example, if both the form and table have a numeric ProfileID, use DLookup("TargetLoSystolic","My Profile","[ProfileID]=" & [ProfileID]). Substitute your actual identifier and verify the result in the temporary text box.

5
Create the color rules

In form Design View, select the Systolic text box and open Format > Conditional Formatting. Add three rules using Expression Is: [Systolic] < DLookup("TargetLoSystolic","My Profile"); [Systolic] >= DLookup("TargetLoSystolic","My Profile") And [Systolic] <= DLookup("TargetHiSystolic","My Profile"); and [Systolic] > DLookup("TargetHiSystolic","My Profile"). Set a font color for each rule, adding profile criteria to every lookup if needed. Apply the rules and test readings below, within, and above the range in Form View.

Correct the field types and conditional formatting rules
Null values do not match the range rules: If a reading or target is Null, the comparison will not evaluate as true. Enter the missing target or add a separate rule for missing values if you want to highlight them.
Free Microsoft Office alternative

Work with Office Documents in WPS Office

WPS Office is a free, lightweight alternative for working with documents, spreadsheets, and presentations. Its familiar interface and Microsoft Office format compatibility make it easier to move everyday files between suites. It does not repair Access form rules or replace an Access database.

Review and format tabular data in WPS Spreadsheets.Open and edit Microsoft Excel XLS and XLSX files, as well as common Word and PowerPoint formats.Use a familiar interface for everyday Office tasks.Move compatible documents between WPS Office and Microsoft Office.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access form text stay black?

The rules may not match if the reading is stored as text, a DLookup returns the wrong target, or a reading or target is Null. Check the field types in table Design View and test each lookup in an unbound form text box.

How do I make DLookup return the current user's target?

Add a criteria argument that identifies the current profile. For a numeric ProfileID, an example is DLookup("TargetLoSystolic","My Profile","[ProfileID]=" & [ProfileID]). Use the actual identifier names in your database.

Where do I change the conditional formatting font color?

Open the form in Design View, select the text box, and choose Format > Conditional Formatting. Edit each rule and select its font color before applying the changes.

Should an empty blood pressure reading be zero or Null?

Use Null when no reading exists, rather than a default of zero. In table Design View, remove the field's zero default and allow Null; then account for missing values in your form rules if needed.