Acc2K - Shading rows in report if criteria is met.

[MindBender77]

Hello All,
I'm not sure if this is possible and have been unsuccessful in my attempts.

Scenerio: I have a report with several fields. If the data in field X = Y then shade the entire row.

I've been attempting to loop through the report using the following in the OnFormat Event:

For Each ctl In Me.Section(0).Controls
    If ctl.Column(4) = "text string here" Then
        Me.Section(0).BackColor = RGB(255, 255, 255)
    Else
        Me.Section(0).BackColor = RGB(192, 192, 192)
    End If
Next
 

Is what I'm attempting even possible in a report?
Bender

[Megalog]

I've used Conditional Formatting to do this. I'm not sure if there's a way though VBA to apply the entire row backcolor at once.

So in your case, you might want to try highlighting the cells in the row you want to format, go to Conditional Formatting, and select "Expression is:"
Then put in your condition: [fieldname]="text string here"
Apply the font color/style, or background fill color, and see if it looks good enough for you.

[FishVal]

Something like the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    ....
    If X = Y Then Me.Detail.BackColor = ....
    .....
End Sub
 

[MindBender77]

I've used Conditional Formatting to do this. I'm not sure if there's a way though VBA to apply the entire row backcolor at once.

Megalog,
Thank you for your quick reply. However, the problem with using conditional formatting is that there are 11 text strings to search for. CF allows for only 3 which is why was I trying this using VBA.

Bender

[MindBender77]

Thank You All, for your assistance.

With some minor changes I was able to shade the rows in the report. Here is the code in case it can assist others.

Private Sub Detail_Format(Cancel as Integer, FormatCount as integer)
For Each rec In Me.Reports.Controls
    If X = Y Then
        Me.Section(0).BackColor = RGB(192, 192, 192)
    Else
        Me.Section(0).BackColor = RGB(255, 255, 255)
    End If
Next
 

Thanks Again,
Bender