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

Posted on 2010-03-23
Medium Priority
Last Modified: 2013-11-28
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

Question by:keimpe
LVL 38

Expert Comment

ID: 28338724
try changing line 10 of your code from this:
          With Me.FieldName.FormatConditions(0)
to this:
          With Me.FieldName.FormatConditions(objFrc)
LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 28338738
Set objFrc = Me![FieldName].FormatConditions.add(acExpression, , ("FieldName.Column(1) = 'SomeTextString'"))

Author Closing Comment

ID: 31705997
Brilliant, thanks!
LVL 39

Expert Comment

ID: 28341408
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.


Author Comment

ID: 28344041
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.

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question