Nested If, Can I change cell colors? Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
ChicagoBuckeye said:
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
Click to expand...

Select Case Me![text93]
Case "Advertising/Brand"
Me!TextBoxName.BackColor = vbWhite
Case "DirectMail"
Me!TextBoxName.BackColor = vbRed
Case "Inbound TM"
Me!TextBoxName.BackColor = vbBlue
Case "Internet"
Me!TextBoxName.BackColor = vbGreen
Case "Outbound TM"
Me!TextBoxName.BackColor = vbYellow
Case "Statements"
Me!TextBoxName.BackColor = vbMagenta
End Select
 
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)
 
Back
Top