Conditional Formatting background color for more than three

Z

zmenloans

I realize that there is no way to individually color a record in a table, so
I created a form with Conditional Formatting to change the background color
of the record in the form based on certain choices. However, Conditional
Formatting only allows you to choose three conditions, and I have ten.

The column is a drop-down list with the ten choices, and I want the form to
have a different color in the field for each choice. For example, if the
field choice was "Paying", I would like the field green. "Out for Repo" =
Orange, "Restructure" = purple, "Follow-Up" = gray, and so on.

Is there some sort of code I can use on the form for this? I am pretty new
to Access so I'd appreciate any help I can get. Also, if there is a sort of
code, can you tell me exactly where to put the code, because I don't know my
way around so well.

Thanks
 
D

Daniel Pineault

You will need to migrate from Conditional Formatting to VBA Coding to do this.

Remove you existing Conditional Formatting.
Open the form in design view

Add a rectangle to your form. You want it to encompass the entire form.
Open the properties dialog (right-click on the rectangle and select
properties) and on the Fomat Tab set the Back Style to Normal. You can set
other properties as you need.

Next, Select the Rectangle and from the main tool bar select Format - Send
to Back (so it is behind your other controls and only in the background.

Now click once on the little square that appear between the horizontal and
vertical rules (it will then appear as a black square) The properties dialog
will now be displaying the properties for the overall form. Goto the Event
tab and select the Current event. Click on the '...' that appear to the right
Now you are in the VBE to start writting your VBA Code. Let the fun begin.

Let assume your drop-down (in access it is called Combo box) is named Status
then your code would look something like

Private Sub Form_Current()
Dim sStatus As Variant 'To account for null values
sStatus = Me.Status

Select Case sStatus
Case "Out for Repo"
Me.BkgColor.BackColor = RGB(0, 0, 255)
Case "Restructure"
Me.BkgColor.BackColor = RGB(0, 255, 0)
Case "Follow-Up"
Me.BkgColor.BackColor = RGB(255, 0, 0)

...

Case Else
Me.BkgColor.BackColor = RGB(125, 125, 125)
End Select
End Sub

To help you figure out which RGB values you need check out the sample db
available at http://www.devhut.net/index.php?lang=en&id=0000000007#colors.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Z

zmenloans

Thanks Daniel!

I still have another question. I have a split form and I want the color to
show up on the form and the table field below the form. It does this using
the conditional formatting option but I can't seem to figure it out with the
code you gave me. I was able to edit the code like this so that the ID box
would change colors depending on how I input the Combo Box value without
making a rectangle (my column is called Color_Codes):

Private Sub Form_Current()
Dim sColor_Codes As Variant 'To account for null values
sColor_Codes = Me.Color_Codes

Select Case sColor_Codes
Case "Paying"
Me.ID.BackColor = RGB(0, 0, 255)
Case "Restructure"
Me.ID.BackColor = RGB(0, 255, 0)
Case "Follow-Up"
Me.ID.BackColor = RGB(255, 0, 0)
Case Else
Me.ID.BackColor = RGB(125, 125, 125)
End Select

End Sub

Is there a way to edit the code so that when I pick a value on the Combo Box
in the form, it will change the color of the ID box on the table part of the
split form? or even better, the whole row?

Thanks a lot so far!
 
Z

zmenloans

Or better yet...can we do this in a datasheet form in datasheet view. It
would look like a table but be a form with the updating colors. I tried to
do this on a datasheet form but the colors only showed up in Design view, not
datasheet view. However, you can still do the general Conditional Formatting
in datasheet view so I know there must be a way.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top