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/03/31/apply-conditional-formatting-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