Conditional formatting with more conditions

[Jan Lichtenbelt]

I have a form showing a table with 11 fields en 10 records. I want backcolors for special records in special fields. I have in pricipal more than 3 conditions (not all conditions are shown here), thus I want to write the conditions myself.

The first field name is: "Counter" with values 1 to 10, the second field name is "Solution" (a text field).

Laoding the form with:
Private Sub Form_Load()
Dim objCond As FormatConditions

Me!Solution.FormatConditions.Delete

Set objCond = Me.Solution.FormatConditions.Add(acExpression, , Me.Counter.Value = 3)
Set objCond = Me.Solution.FormatConditions.Add(acExpression, , Me.Counter.Value <> 3)
Me!Solution.FormatCondition(0).BackColor = RGB(255, 0, 0)
Me!Solution.FormatCondition(1).BackColor = RGB(0, 255, 0)

Set objCond = Nothing
End Sub
Fails with the error message Type mismatch for Set ObjCond...

Who can help me to work backcoloring my table in a Form on special places?

Jan Lichtenbelt

 

[pbaldy]

You still can't exceed the 3 conditions. Try something like this in the current event:

Select Case Me.Counter
  Case 1
    Me.Solution.BackColor = vbRed
  Case 2
    ...
End Select

 

[missinglinq]

As Paul said, you're still limited to three conditions (plus the default) using FormatCondition from code. And be aware that Paul's code, which allows for an unlimited number of conditions, will only work for in a Single View Form. In a Continuous View Form all instances of Solution in all records will be formatted according to the formatting in the Current Record. In Datasheet View it'll have no effect at all; in Datasheets you can only use CF from the Formatting Menu.

 

[DCrake]

Here is a link to a sample demo of simulating conditional formatting with more than 3 options

Link here

David

 

[Jan Lichtenbelt]

Dear Paul, thanks for the programming inpromevents.
Dear Missinglinq, that makes for me clear why the whole columns getting coloured instead of special cells.
Dear David, the demo looks nice, but I can not make more out of it than it is a commercial to contact your company. This kind of programming is just my hobby and not commercial. Please contact me if you can help me anyway.

With kind regards

Jan Lichtenbelt

 

[DCrake]

The demo has been taken from my website but if you ignore the "commercial" as you put it, you may find the demo quite useful. I could have simply sent you a link to my web site, but I cut the link out so you could find a solution sooner.

David

 

[Jan Lichtenbelt]

Dear David,


Thanks for your reply. I tried your demo a second time. What I see, is what I like. But it is completely unclear/not possible to find out who you managed this. And I like to learn something, not just seeing a demo. Or did I mis by some reasons the solution?

Jan Lichtenbelt