Conditional Formatting using vba (1 Viewer)

dim

Registered User.
Local time
Today, 00:41
Joined
Jul 20, 2012
Messages
54
Hi,

I have a form where I need a conditional formatting on a field [TT].
So if I use a basic way to do it (condition 1), the expression is [CAV]-[TTA]>0 is working fine and the back color become green as I need.

I can't use that way, because this is an indicator which I don't want to show all the time.

So I tried do it using vba with a button Command143:

Private Sub Command143_Click()
FormatConditions.Add acExpression, acEqual, "IIf([CAV]-[TTA]>0, True, False)"
FormatConditions(1).BackColor = vbGreen
End Sub

But I get a runtime error 404: “Object required”

I never use this way before…Can you help me please?

Thanks
 

June7

AWF VIP
Local time
Yesterday, 20:41
Joined
Mar 9, 2014
Messages
5,424
I expect you have to identify the control.

Private Sub Command143_Click()
Me.controlname.FormatConditions.Add acExpression, acEqual, "IIf([CAV]-[TTA]>0, True, False)"
Me.controlname.FormatConditions(1).BackColor = vbGreen
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:41
Joined
May 7, 2009
Messages
19,169
if you keep hitting the button you will just adding and adding the FC.


Private Sub Command143_Click()
With [TT].FormatConditions
.Delete
With .Add(acExpression, acEqual, "(([CAV]-[TTA])>0)")
.BackColor = vbGreen
End With
End With
End Sub
 
Last edited:

dim

Registered User.
Local time
Today, 00:41
Joined
Jul 20, 2012
Messages
54
Hi,

Is working!!!
Thank you very much!
Have a nice day!
 

aindoe

New member
Local time
Today, 12:41
Joined
Feb 27, 2019
Messages
8
how about comparing the field of the report (say it reportField) with another field from a table (tableField)?
I tried to just use the built-in conditional formatting under the Format ribbon but it does not work.



i want reportField to be highlighted in red if it exceed tableField.



if you keep hitting the button you will just adding and adding the FC.


Private Sub Command143_Click()
With [TT].FormatConditions
.Delete
With .Add(acExpression, acEqual, "(([CAV]-[TTA])>0)")
.BackColor = vbGreen
End With
End With
End Sub
 

June7

AWF VIP
Local time
Yesterday, 20:41
Joined
Mar 9, 2014
Messages
5,424
@aindoe, if you have a question, should start your own thread instead of hijacking an old one. Use DLookup() or join tables in query.
 

Micron

AWF VIP
Local time
Today, 00:41
Joined
Oct 20, 2018
Messages
3,476
Never tried this so I'm curious. If the inner and outer With both apply to .FormatCondtions, why nest it? Wouldn't this work?
Code:
With [TT].FormatConditions
  .Delete
  .Add(acExpression, acEqual, "(([CAV]-[TTA])>0)")
  .BackColor = vbGreen
End With
 

Users who are viewing this thread

Top Bottom