What is this recordset doing? The recordset should be a collection of data not interacting with the form really.

Usually when I'm interacting with the form colors I'd do something like

    Dim Ctl As Control
    For Each Ctl In Me.Controls
        If TypeOf Ctl Is TextBox Or TypeOf Ctl Is ComboBox Then
            If Ctl.Locked = True Then
                Ctl.BackColor = RGB(225, 225, 225)
            ElseIf Ctl.Locked = False Then
                Ctl.BackColor = RGB(255, 255, 255)
            End If
        End If
    Next Ctl

This is what I'm trying to do, and the result that I posted was from ChatGPT.

Write VBA for Private Sub SortOrderBackgroundColor() that will change the background color of the field "SortOrder"  for all records as follows:

Set all to #BFBFBF.  
If today is a MONDAY and SortOder = 1, then set the background color of SortOrder to yellow
If today is a Tuesday and SortOder = 2, then set the background color of SortOrder to yellow
If today is a Wednesday and SortOder = 3, then set the background color of SortOrder to yellow
If today is a Thursday and SortOder = 4, then set the background color of SortOrder to yellow
If today is a Friday and SortOder = 5, then set the background color of SortOrder to yellow
If today is a Saturday and SortOder = 6, then set the background color of SortOrder to yellow

So you're only really dealing with one field here, right? SortOrder? And it has a value from 1 to 7 based on the day of the week? You've got 1=Monday, whereas normally 1=Sunday, so that's OK. We just need a little math.

SortOrder.BackColor = vbWhite ' or whatever you want
If Weekday(Date())=SortOrder+1 Then SortOrder.BackColor = vbRed ' or whatever

That should do it. No Ifs, Recordsets, For Each, Controls, etc. needed.

Is this a continuous form?

Conditional formatting might be better for this, could you upload a picture so i can try to see what your doing on the form?

Oh, yes, CF would be REQUIRED for a continuous form. In that case, just use my logic for the CF formula.

Yeah. Use Conditional Formatting. Value = Weekday(Date-1)

It would be even easier if you made Monday=2. Just sayin.

Thanks Rick, I'll see if I can figure that out because it is a continuous form.

And making Monda a 2 is not a problem.  

Yeah, only because with the Weekday function, the start of the week (Sunday) is 1, you can change that with the options, but it's just easier if you deal with what they give you. Then just have the conditional formatting say if today's weekday is the same as the value in this field, then change the color.

Thank you Adam and Richard.