Apply Conditional Formatting to Entire Row 1

[MeldrethMan]

I'd like to highlight a row in a report according to one field's value.

So if the details section shows a record with Name, Type and Days To Renewal I want all three to display in bold red when Days to Renewal is less than 60.

My reading of Conditional Formatting is that it applies to one field only.

 

[sxschech]

In vba you could write an if statement something like below. However, this was written for Access 2003, so if you are using a newer version, there may be an easier way. Haven't looked into it yet as haven't needed to change my code yet. This changes the backcolor, but you can also change other properties for color, bold etc. It goes in the format or print event of the section where you want the effect, such as the detail section of the report.

  If Me.FieldName < 60 Then
        Me.Box200.Visible = True
        Me.txtbox1.BackColor = 12632256
        Me.txtProgramCampus.BackColor = 12632256
        Me.txtCOLLEGE.BackColor = 12632256
        Me.txtApplied.BackColor = 12632256
        Me.txtAccepted.BackColor = 12632256
        Me.txtIntend.BackColor = 12632256
        Me.txtAppliedYr03Yr02.BackColor = 12632256
        Me.txtAcceptedYr03Yr02.BackColor = 12632256
        Me.txtIntendYr03Yr02.BackColor = 12632256
..

 

[MajP]

select the 3 controls. Select conditional formatting. This will apply that condition to all three. Now chosse
Expression is: [yourRenewalFieldName] < 60

 

[MajP]

Also you can put a textbox that spans the row and place it behind all the other textboxes. Then you can apply the condition to that textbox. This will make the "whole Row" hilited instead of the controls in the row.

 

[sxschech]

If you try MajP's suggestion, (which should be easier, and I forgot about) be sure that the textboxes of display data are transparent so the color shows through, otherwise you will have white boxes interspersed with the colored background.

 

[MeldrethMan]

Thanks so far. What I'm not clear about is using the condition in one field to apply formatting to another one.

When selecting all three fields in my Details section the Conditional Formatting rule says 'Format only cells where ...'
The result was that just the number field showed the alternative format.

 

[MajP]

Then click on the other controls and repeat.

 

[MeldrethMan]

I'm missing something here, sorry.

When going to anther control the Conditional Format setup only allows response to its value, not to the numerical field I want to use as a trigger for changing the colour of the entire row.

 

[MajP]

http://office.microsoft.com/en-us/a...using-conditional-formatting-HA010208133.aspx

See section
ADD CONDITIONAL FORMATTING BY USING AN EXPRESSION

 

[MeldrethMan]

Right, got it, thanks! I'd missed seeing the option to use 'Expression is'.

Sorry that took a while, and thanks for your patience.