More Than 3 Conditional Format

[depawl]

I have a report with a list of employee names (and other information). A handful of them need to be a different color (other than default). There will always be more than 3, so I can't use the conditional formatting tool. I've seen elsewhere on this forum where someone developed a subform but that doesn't help with a report. I presume this could be done with VBA but need a little help to get me going.
Thanks.

 

[pbaldy]

This should get you going:

Select Case WhateverYouWantToTest
  Case FirstValue
    Me.ControlName.Backcolor = NumericalValueOrConstant
  Case SecondValue
    ...
End Select

 

[depawl]

Seems I would need something like an If/Else statement?
If Me.ControlName = (the employees who need a different text color)
ControlName.TextColor = NumericalValueOrConstant
Else
ControlName.TextColor = default (Black)

?

 

[pbaldy]

You can do it with If/Then/Else or Select/Case, as you prefer.

 

[depawl]

If I use Select Case, don't I have to list every value (i.e. - every employee name)? The list could be 50+ names long.
Which is why I thought of using if/then/else.

 

[pbaldy]

You'd have to list them all in an If/Then/Else too, unless you use the Else clause to catch unlisted options. Select/Case also has an Else clause, so like I said, you can use either, as you prefer.

 

[Galaxiom]

Maintain a table that lists the IDs which are to be formatted differently.

This table would be joined into the query that is used as the RecordSource and return a code that used as the basis for conditional formatting (or its equivalent).

 

[depawl]

Well I'm not starting off too well.
The employee names are in the format:
Last Name, First Name, Middle Initial-Employee ID
I keep getting an error for the statement:
If Me.Employee_Name = Doe, John J-12345
Tried enlosing the name in quotes, etc. No luck.

 

[pbaldy]

It certainly needs quotes. What's the actual code and what's the error description?

 

[depawl]

See attached.

 

[pbaldy]

You forgot the "Then".

 

[pr2-eugin]

On top of that, OP forgot to put the If inside a Method might I add?

 

[pbaldy]

On top of that, OP forgot to put the If inside a Method might I add?

Details, details.

For what you're doing, probably the detail section's format event:

http://www.baldyweb.com/FirstVBA.htm

 

[depawl]

OK, sorry I thought the "Then" went on the next line. So moving forward
I have the attached dilemma.
Also, pr2, I don't understand your "inside a Method" comment.
Thanks again.

 

[pr2-eugin]

Also, pr2, I don't understand your "inside a Method" comment.

As Paul has attached a link to his site on how to write a first VBA code. Look at it (http://www.baldyweb.com/FirstVBA.htm).

You need to put your code inside the Detail's On Format method. What you have done is just added VBA code behind the Report. So do this, go to the Design view of the report. Click on the Details section i.e. the Body of the Report. And on the Properties Window there should be Events tab. Under events, there is one event On Format, from there follow the link provided. You should use your If inside the method..

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Employee_Name = "Doe, John R-12345" Then
        Me.Employee_Name.BackColor = [COLOR=Red][B]&H[/B][/COLOR]FCE6D4
    Else
        Me.Employee_Name.BackColor = [COLOR=Red][B]&H[/B][/COLOR]FFFFFF
    End If
End Sub

 

[depawl]

Got it. Thanks.
Since there are more than 1 employee that need to be selected (with a different color background) does each one need his/her own If/Then/Else Statement, or can they be combined?
For example:
If Me.EmployeeName = "Doe, John R-12345" or "Smith, Susie L-54321"
etc.

 

[pr2-eugin]

Can be combined.. Just make sure it is in the right syntax..

If Me.EmployeeName = "Doe, John R-12345" Or [COLOR=Red][B]Me.EmployeeName = [/B][/COLOR]"Smith, Susie L-54321"

 

[pbaldy]

One of the reasons I initially suggested Select Case is the simpler syntax for this than If/Then:

Case "Doe, John R-12345", "Smith, Susie L-54321"

 

[depawl]

OK, thanks much guys, I think I got it. Just out of curiosity, it seems the formatting (colored background) only shows up in Print Preview but not in Report View. Any idea why?

 

[pbaldy]

The detail format event does not fire in Report view. I've found that view to be of limited use.