Conditional Formatting for Multiple Text Boxes with VBA for More than 3 Conditions

[jollofrice]

I have a large number of calculated text boxes in an Access 2007 report that I want to apply conditional formatting to for more than 3 conditions. I have been trying to use a Collection to allow me to loop through the text boxes and apply the formatting.

I have the following code attached to the OnFormat event for the section that holds the text boxes. The code does not generate any compile or run time errors, but the text boxes do not get formatted. The format code worked fine when applied to individual controls, but that was too much work. What am I doing wrong?

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
Dim Gold As Long
Dim DarkBlue As Long
Dim MedBlue As Long
Dim LightBlue As Long
Dim ColorSet As String

Dim TargetControls As Collection

Set TargetControls = New Collection

TargetControls.Add TextBox1
TargetControls.Add TextBox2
TargetControls.Add TextBox3
'Etc for all text boxes


'There are two color sets. I set this variable to janis for a color scheme designed by a co-worker
ColorSet = janis


'Select Color Set

'Janis' Color Set
If ColorSet = janis Then
DarkBlue = RGB(255, 153, 204)
MedBlue = RGB(255, 255, 153)
LightBlue = RGB(141, 180, 227)
Gold = RGB(181, 249, 183)
Else

'My Color Set
Gold = RGB(198, 171, 2)
DarkBlue = RGB(15, 31, 72)
MedBlue = RGB(142, 163, 189)
LightBlue = RGB(223, 229, 237)
End If


'Format Target Controls
Dim VariableControl As Variant

For Each VariableControl In TargetControls
If IsNull(VariableControl) = True Then
VariableControl.BackColor = vbWhite
VariableControl.ForeColor = vbBlack
ElseIf VariableControl <= 30 Then
VariableControl.BackColor = DarkBlue
    If ColorSet = janis Then
    VariableControl.ForeColor = vbBlack
    Else
    VariableControl.ForeColor = vbWhite
    End If
ElseIf VariableControl <= 60 Then
VariableControl.BackColor = MedBlue
VariableControl.ForeColor = vbBlack
ElseIf VariableControl <= 75 Then
VariableControl.BackColor = LightBlue
VariableControl.ForeColor = vbBlack
ElseIf VariableControl <= 100 Then
VariableControl.BackColor = Gold
VariableControl.ForeColor = vbBlack
Else
VariableControl.BackColor = vbWhite
VariableControl.ForeColor = vbBlack
End If
Next VariableControl

End Sub

 

[wiklendt]

can you please edit your post to wrap your code in code tags to make it easier to read, please?

 

[ajetrumpet]

can you please edit your post to wrap your code in code tags to make it easier to read, please?

couldn't agree more.

 

[jollofrice]

Oops. Sorry about that.

 

[jollofrice]

I've figured it out. The problem was not the code, but, naturally, the calculated text boxes. It took a long time to get the query right, but now it's working fine.

Moral for future readers: If it needs to be calculated, it should be done with a query.