Color Coding a Report

Question

Is there a way to color code reports? For instance if a certain yes/no question is answered yes for a row, then that row would be yellow or if a this box is checked as well as this other box then the row would be red etc.?

Answer 1

I went to the event tap in the detail property sheet clicked on the ... in the on format row and visual basic came up and I put it in there were the first and last line were already there (I deleted the duplicate first and last line). I changed the default to white. And the orange to RGB (255, 245, 238).

Answer 2

Did you enter the code by first selecting the Format event property in the detail section's properties sheet, or did you enter it all directly into the report's module.  It must be the former, in which case when it takes you to the event procedure the first and last lines will already be in place.

BTW do you really want Cyan as the default?  I just used it as an example.  Also there is no vbOrange constant; you'll need to build a colour using the RGB function (look it up in Help),  or use another one for which there is a constant, e.g. vbMagenta.

Answer 3

This is what I entered, I didnt get any change. WHat am I doing wrong?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    ' first set default backcolor

    Me.Section(acDetail).BackColor = RGB(0, 255, 255)

    ' set  backcolor dependent on values in controls

    If Me.[Fail P/U?] = True Then

        Me.Section(acDetail).BackColor = vbYellow

    End If

    ' set  backcolor dependent on values in controls

    If Me.[Fail S/U?] = True Then

        Me.Section(acDetail).BackColor = vbYellow

    End If

    ' set  backcolor dependent on values in controls

    If Me.[Fail Run?] = True Then

        Me.Section(acDetail).BackColor = vbYellow

    End If

    ' set  backcolor dependent on values in controls

    If Me.[Scored < 90on P/U?] = True Then

        Me.Section(acDetail).BackColor = vbBlue

    End If

    ' set  backcolor dependent on values in controls

    If Me.[Scored < 90 S/U?] = True Then

        Me.Section(acDetail).BackColor = vbBlue

    End If

    ' set  backcolor dependent on values in controls

    If Me.[Scored < 90 on Run?] = True Then

        Me.Section(acDetail).BackColor = vbBlue

    End If

    ' set  backcolor dependent on values in controls

    If Me.[Permanent Profile] = True Then

        Me.Section(acDetail).BackColor = vborange

    End If

    ' set  backcolor dependent on values in controls

    If Me.[Did Not Take?] = True Then

        Me.Section(acDetail).BackColor = vbRed

    End If

 If Me.[Scored < 90 on Run?] = True And Me.[Scored < 90 S/U?] = True And Me.[Scored < 90on P/U?] = True Then

        Me.Section(acDetail).BackColor = vbGreen

    End If

End Sub

Answer 4

If you want to conditionally set the background colour colour of the whole of the detail section rather than the text or back ground colours of individual controls (for which you'd use conditional formatting), first make sure that the BackStyle property of all controls in the section is set to Transparent.  Then put code in the detail section's Format event procedure:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    ' first set default backcolor

    Me.Section(acDetail).BackColor = RGB(0, 255, 255)

    ' set  backcolor dependent on values in controls

    If Me.[SomeControl] = True Then

        Me.Section(acDetail).BackColor = vbYellow

    End If

    If Me.[SomeControl] = True And Me.[SomeOtherControl] = True Then

        Me.Section(acDetail).BackColor = vbRed

    End If

End Sub

Note that the order of the If....End If constructs is important here.  Not all colours can be represented as constants, so you may need to use the numerical value for the colour in some cases, or, as I've done with the default backcolor here, use the RGB function, which in the above case sets it to Cyan.

Answer 5

You can use Conditional Formatting to do that. I'm using Acc 2010 and if I open a report in design view the conditional formatting icon is under the Format tab.