Changing the Color of the Rows of a Form Based on Values Stored in a Table (Access VB

[JM11]

Hello,
I am currently working on an access based application where I need to change the color of some specific rows of a form (the color of "one" field per row needs to be changed) based on values stored in these fields. For instance, when we open the form, we need to see color green in the field of a certain row where we have the value 1. If the value of this field is 2, we need to see color orange and if it is 3 it is red.

Example of the output of the form
------------------------------------

ID | Criteria
201 | 1 --> the cell containing 1 should be colored in green
203 | 3 --> the cell containing 3 should be colored in red
204 | 3
205 | --> the cell that contains nothing should be kept uncolored
206 | 1
207 | 2


Note: the values (1, 2 and 3) are already available when the form opens and they are also stored in a table.


Thank you for your help!

[jforbes]

JM11,
Conditional Formatting should do the trick: https://support.office.com/en-sg/art...5-44e43b05e22f

Conditional Formatting can be a little quirky, but it can accomplish color coding that can't be accomplished in any other way natively in Access.

[zmbd]

Microsoft Tech Support: Change the appearance of a control by using conditional formatting

If that doesn't work then we'll need some VBA or Macro to do the magic.

[JM11]

Thank you so much for your replies,
I actually did conditional formatting and the issue was solved!
Attached is the code that might be helpful if anyone has the same question.

Regards,

Option Compare Database
Option Explicit
 
Const GreenG As Byte = 1
 
Const OrangeO As Byte = 2
 
Const RedR As Byte = 3
 
 
Private Sub StartCondFormatting()
 
Dim objFrc As FormatCondition
 
    Const orange As Long = 42495
 
    Const green As Long = 25600
 
    Const red As Long = 255
 
    Dim ctl As Control
 
 
 
    For Each ctl In Me.Controls
 
        If ctl.Tag = "Conditional" Then
 
            With ctl
 
                'Remove format conditions
 
                .FormatConditions.Delete
 
 
 
                'Create three format objects and add them to the FormatConditions
 
                Set objFrc = .FormatConditions.Add(acExpression, acEqual, "[fieldName] = " & GreenG)
 
                Set objFrc = .FormatConditions.Add(acExpression, acEqual, "[fieldName] = " & OrangeO)
 
                Set objFrc = .FormatConditions.Add(acExpression, acEqual, "[fieldName] = " & RedR)
 
 
 
                'Specify the formating conditions
 
                .FormatConditions(0).BackColor = green
 
                .FormatConditions(0).Enabled = True
 
                .FormatConditions(1).BackColor = orange
 
                .FormatConditions(1).Enabled = True
 
                .FormatConditions(2).BackColor = red
 
                .FormatConditions(2).Enabled = True
 
            End With
 
        End If
 
    Next ctl
 
    Set objFrc = Nothing
 
 
    End Sub
 
 
 
Private Sub Form_Load()
StartCondFormatting        
End Sub

https://accessexperts.com/blog/2011/...ng-using-code/

[zmbd]

Glad you found a solution; however, IF you follow the link provided, and you are running one of the newer versions of Access (post v97), you do not need any VBA code for many conditional formatting options. For the average user this is the route I recommend... however, the code approach is so much more flexible :)
-z