Loop over rows of a form (Access VBA) Conditional Formatting

[JM11]

Hello,
I would like to loop over the rows of a form and check in each row the value of 1 column; depending on this value, the cell (for the corresponding row and column) should change color. This procedure should be done when the form is opened so that we can see the colored cells as soon as we open the form.
Thank you for your help,
Regards

[MikeTheBike]

Hi

Have you considered conditional formating?
This would seem to be the application for which it is designed!

If you need any help with that then let us know.

MTB

[JM11]

Thank you so much for your reply,
I did not know about conditional formatting (I am new to access VBA); I did a search on it and now it is working perfectly!
Attached is the code; it could be helpful for people who might have 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/

[MikeTheBike]

I am glad that helped, but I must confess I have never seen or considered doing it in code. That in itself is very interesting.
I have always set conditional formatting in design view.

As they say, you learn something new every day (well almost every day!).

MTB

[NeoPa]

Up until 2003 there were only ever three settings for Conditional Formatting (2007 increased that to a more useful number). In that situation it made sense for various experts to develop and share code-based techniques that could do a similar job.

Since 2007 they're now mostly redundent.