Conditional formatting in VBA code applies to all rows of a datasheet form

Dear Experts,

I'm trying to re-create the conditional formatting of a field on a form (that is shown in datasheet view) in code, because I want a specific background color that can't be selected in the conditional formatting popup.

However, when I set conditional formatting in code, this field is set to this new custom background color on all the rows of the datasheet, even the ones for which the expression is false.

The attached image shows the Conditional formatting that works perfectly (but with the wrong color) and the attached code shows the conditional formatting that looks perfectly (but on all the rows).

Can you help me?

One remark: the field I'm using for conditional formatting is a drop down combobox where the text in the second column determines which background color should be used for the field. I don't know if this is important, but I thought I'd mention it just in case.

Keimpe Wiersma
Dim objFrc As FormatCondition
    Dim lngBlue As Long

    lngBlue = RGB(159, 210, 254)

    Set objFrc = Me![FieldName].FormatConditions.add(acExpression, , (FieldName.Column(1) = "SomeTextString"))
    With Me.FieldName.FormatConditions(0)
        .BackColor = lngBlue
    End With

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

try changing line 10 of your code from this:
          With Me.FieldName.FormatConditions(0)
to this:
          With Me.FieldName.FormatConditions(objFrc)
Set objFrc = Me![FieldName].FormatConditions.add(acExpression, , ("FieldName.Column(1) = 'SomeTextString'"))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
keimpeAuthor Commented:
Brilliant, thanks!
Set up the conditional formatting as you show in your screen shot in design view. Then in the form's on load event put:
Private Sub Form_Load()
Me.[FieldName].FormatConditions(0).BackColor = RGB(159, 210, 254)
End Sub

I don't know what you are trying to do with the line:
    Set objFrc = Me![FieldName].FormatConditions.add(acExpression, , (FieldName.Column(1) = "SomeTextString"))
It is also missing a closing parentheses.

RGB(159, 210, 254) is extremely close to the conditional format color circled in the attached image.

keimpeAuthor Commented:
Thanks, thenelson! Your solution works just as well. Too bad I have already awarded the points, otherwise I could have split them.

And about the two colors being extremely close to eachother: I know! But this application is designed to run at the graphic design department of an advertising agency. Anytime anything isn't exactly the way they want it, they start shouting "APPLE, APPLE!" at me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.