Nested If, Can I change cell colors? Help

C

ChicagoBuckeye

Hello.
I am trying to create a way in which the background color of a text box
within an Access 2000 report can change to anyone of 6 colors based on the
results of another box. Can this be done with Nested If's? If so how would I
revise this (Because right now it works great indicating the correct number.
But I want it to change the background color not the number) :

=IIf([text93]="Advertising/Brand",1,IIf([text93]="Direct
Mail",2,IIf([text93]="Inbound
TM",3,IIf([text93]="Internet",4,IIf([text93]="Outbound
TM",5,IIf([text93]="Statements",6,""))))))

Please Advise. Thanks in Advance!
-ChicagoBuckeye
 
D

Duane Hookom

You can use code in the On Format event of the report section like:
' First change the name of text93 to "txtHow" and make sure the background
is not set to transparent.

Select Case Me.txtHow
Case "Advertising/Brand"
Me.txtHow.BackColor = vbRed
Case "Direct Mail"
Me.txtHow.BackColor = vbGreen
'more cases

Case Else
Me.txtHow.BackColor = vbWhite
End Select

Ideally, you would have a field [BGColor] in your table of each of these
unique values that stores the color of the background. You can add the
[BGColor] field to your report's record source and use code like:
Me.txtHow.BackColor = Nz(Me.BGColor,vbWhite)
 
Top