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/...ng-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.