Conditional formatting based upon the results of a formula

[Demian Valle]

I have a report that looks like this:

Sun Mon Tue Wed Thu Fri Sat Total Percent
7:00 4 5 3 12 4 3 11 42 5.45%
7:30 4 3 3 19 1 12 10 52 6.73%
8:00 12 10 10 10 18 2 2 64 7.01%

etc.

The "Percent" column is based upon the following formula:
[total]/sum([total]).

I would like to change the color of all the fields in the
record with the highest value in the "Percent" column to
red. Does anyone have any advice on how to do this with
the conditional formatting included in Access or via code?

Thanks.

 

[Duane Hookom]

Add a text box to the detail section
Name: txtMaxTotal
Control Source: =Max([Total])
Visible: No
Then add this code to the On Format event of the detail section.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Section(0).Controls
If Me.Total = Me.txtMaxTotal Then
ctl.ForeColor = vbRed
Else
ctl.ForeColor = vbBlack
End If
Next
End Sub