Have you ever needed to work with Conditional Formatting via VBA and tried to iterate through the FormatConditions collection?! Did you get incomplete results? Well, there is apparently a serious bug depending on the approach taken.

The easiest approach to iterating over the collection is to do something like the following which is to iterate over the FormatConditions object:

Dim oFC As FormatCondition
Dim i As Long

For Each oFC In Me.FirstName.FormatConditions
    i = i + 1
    Debug.Print i, oFC.Expression1
Next oFC

Yet, this will fail to list more than 3 Format Conditions! Yep, you read that right. It will not necessarily list all the conditions. Making it worse, is I found no mention of this in the documentation (see below – maybe I missed it?!).

The Solution

Instead, the way to approach working with FormatConditions is to specifically use the Count property to iterate over the collection. So, you need to do something more like:

Dim i As Long

For i = 0 To Me.FirstName.FormatConditions.Count - 1
    Debug.Print i + 1, Me.FirstName.FormatConditions(i).Expression1
Next i

Case And Point

To illustrate this issue, I create a simple set of Conditional Formatting rules on a textbox and created the following:

And used both of the above code blocks to iterate over the conditional formatting rules to output some information to the immediate window.

Using the FormatConditions object approach, we get:

 1            "Marvin"
 2            "Joanne"
 3            "Larry"

Using the FormatConditions.Count approach, we get:

 1            "Marvin"
 2            "Joanne"
 3            "Larry"
 4            "Carrie"
 5            "Daniel"

Thus, as stated above, iterating over the FormatConditions object can possibly induce error by limiting the returned collection to only 3 rules (seems to be the 1st three rules that get returned)!

So just keep this in mind when programming as I was working on a project a short while ago and it took me a while to figure this out. I never thought that the issue was with the FormatConditions object and was sure it was my code. Yet, the inverse turned out to be true.

Thank you to Shane for mentioning this in a recent conversation as it refreshed my memory on the matter as I hadn’t made a blog posting at that time that I encountered this bug.
 

Additional Resources

FormatConditions object (Access)

The FormatConditions collection represents the collection of conditional formats for a combo box or text box control. Each format is represented by a FormatCondition object.