Change backcolor based on multiple if/then conditions

[scottb]

I'm working in Access 2007. Trying to build form (frmPatientMain) with a tab control for a database used by a rural clinic to track immunizations. There are two tabs: Main and Grades. Different immunizations are due depending on the child’s age in days (txtTxtDaysOld)
There are a total of 14 immunizations tracked. On the Main tab there are fields to track which of those 14 immunizations have been given as appropriate to the child's age in days. I am trying to determine if one or more immunizations have been missed based upon the age of the child and change the back ground color of the immunization text labels to red to alert the clinician that those have been missed. For any that have not been missed I would like to keep the default/automatic background color.
I am trying something along the lines of:

Private Sub Form_Open(Cancel As Integer)
If txtDaysOld >= 30 And txtDaysOld <= 45 And BCG_Received Is Null Then
lblBCG.BackColor = ED1C24
Else
lblBCG.BackColor = FFFFFF
End If

If txtDaysOld >= 30 And txtDaysOld <= 45 And ORI_Received Is Null Then
lblORI.BackColor = ED1C24
Else
lblORI.BackColor = FFFFFF

End If
If txtDaysOld >= 50 And txtDaysOld <= 75 And Polio1_Received Is Null Then
lblPolio1.BackColor = ED1C24
Else
lblPolio1.BackColor = FFFFFF
End If

If txtDaysOld >= 125 And txtDaysOld <= 150 And Vita1_Received Is Null Then
lblVita1.BackColor = ED1C24
Else
lblVita1.BackColor = FFFFFF
End If
Etc..
Is this the appropriate approach for evaluating multiple conditions and would this work? Any direction would be greatly appreciated.
Thank you!

[agibsonsw]

I believe in VBA you need to use &H for hex colours: &HED1C24.

You could also place

Const DEFAULTC = &HFFFFFF

at the top of the module and use DEFAULTC in place of the hex number within your code. This will make it easier to work with.

[HansV]

Instead of VBA code, I would use conditional formatting, but you can do that only for text boxes (and combo boxes), not for labels.

The description below applies to the BCG_Received etc. text boxes, but if you prefer, you can change lblBCG to a text box with Enabled = No, Locked = Yes and Control Source ="BCG" (or similar). It would look like a label but still be a text box, so you could apply conditional formatting to it.

Open the form in design view.
Select BCG_Received.
Click Conditional in the Font group of the Design tab of the ribbon.
Under Condition 1, select Expression Is from the dropdown.
Enter the following expression in the box next to it:
([txtDaysOld] Between 30 And 45) And [BCG_Received] Is Null
Click the Fill/Back Color and/or Font/Fore Color buttons to specify the formatting to be applied if the condition is satisfied.
Finally, click OK.

Repeat for the others.

Conditional formatting will work in single and continuous forms (and in datasheet forms), whereas code such as you proposed will work in a single form only.