Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conditional Formatting Problem - VBA 1

Status
Not open for further replies.

JStrand

Technical User
Nov 14, 2011
44
US
I have a report with Field Name = [WO_GrossRev_Var1] which is in the Detail Section of the report. The Datasouce contains a hundred records or so. I want each record on the report to have a BackColor of Yellow (RGB(255, 255, 0))if the value is <-0.0050 else black. On the On Format Event of the report detail, I've added the following procedure which produces no conditional formatting effect:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim MyWOGrossRevVar As Double
MyWOGrossRevVar = [WO_GrossRev_Var1]
If MyWOGrossRevVar < -0.005 Then
[WO_GrossRev_Var1].BackColor = RGB(255, 255, 0)
Else
[WO_GrossRev_Var1].BackColor = RGB(255, 255, 255)
End If

End Sub


Can anyone see the problem with this code? Thank you in advance.
 
Thanks MajP. I used your suggested method first and strangely it will apply a bold type face when the condition is met, but won't apply a backcolor of yellow. Therefore I tried the VBA method on the report detail section. There must be some other backcolor property overriding the conditional format I'm attempting to apply.
 
Backstyle = transparent
backcolor = #FFFFFF
 
Changing Backstyle = normal does the trick. Thanks Duane.
 
I have an additional condition format to be applied to the problem above which I need some help with. The test above was for a format to be applied (backcolor = yellow)if a condition was met for a specific period. Now I need an additional format to be applied (backcolor = red) if the same condition is met in two successive periods, or yellow if it is the first occurrence.

I went back to the VBA method and it works fine for the first period test but I need some help on the SQL required for the additional test. I've reduced the SQL below for just the first field which is tested. I receive an error message upon execution: Object doesn't support this property or method. I'm pretty sure I'm mis-using the If / And method. If you have any suggestions I would appreciate it.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyWOGrossRevVar As Double
Dim MyWOGrossRevPP As Double

MyWOGrossRevVar = WO_GrossRev_Var1
MyWOGrossRevPP = WO_GrossRev_PP

If MyWOGrossRevVar < -0.05 And MyWOGrossRevPP < -0.05 Then
WO_GrossRev_Var1.BackColor = RGB(255, 125, 125)
Else IF MyWOGrossRevVar < -0.05 Then
WO_GrossRev_Var1.BackColor = RGB(255, 255, 0)
Else
WO_GrossRev_Var1.BackColor = RGB(255, 255, 255)
End If

End Sub
 
I was missing a name on my control source and this SQL appears to work for the second condition. If you see any other problems please advise. Thank you..
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top