Conditional Formatting with VBA

[bwc]

I am trying to write a public function that will do conditional formatting, but I'm stuck. When I step through the code, it applys the correct forecolor, but to all the records; if the field is < then 0, all the records are red; if the field is >0, all the records are green...

Public Function redGreen(F As Form, fieldName As String)
    Dim rs As Recordset
    Set rs = F.Recordset
    
    rs.MoveFirst
    
        Do Until rs.EOF
            
                If F(fieldName).Value > 0 Then
                    F(fieldName).ForeColor = vbGreen
                ElseIf F(fieldName).Value < 0 Then
                    F(fieldName).ForeColor = vbRed
                End If
            
            rs.MoveNext
        Loop
    rs.Close
    Set rs = Nothing

End Function

 

[Gasman]

Continuous form?
Where are you calling it from?

 

[June7]

That's the way programmatically setting properties in form works. Can do this with report.

Why not use Conditional Formatting in design instead of VBA? This is exactly what it's for.

Or use Format property to conditionally change forecolor if this is a number field. https://www.access-programmers.co.uk/forums/threads/changing-font-color-based-on-value.220459/

 

[theDBguy]

I am trying to write a public function that will do conditional formatting

To create conditional formatting using VBA, you can use the FormatCondition object.

 

[bwc]

Continuous form?
Where are you calling it from?

Private Sub Form_Current()
 redGreen(Me,"subCatTotalTbx")
End Sub

 

[MajP]

I am assuming this is a conditional form and therefore this will not work. In order to get different conditions per record you have to use conditional formatting. That is not conditional formatting and every record will be one color, unless you use the paint event which can be problematic.
Use the conditional format gui to set your formatting rules for the control.

 

[MajP]

If you need to set the rules dynamically during run time you would need to do something like this, but my guess you simply need to use the CF gui.

Solved - Programatically change color on continous form conditional formatting

My ORIGINAL post, all the way back at #3 suggested a function might work. The OP wasn't buying and everyone else is suggesting that VBA will work.

www.access-programmers.co.uk

 

[bwc]

To create conditional formatting using VBA, you can use the FormatCondition object.

how do I do that?

 

[GPGeorge]

how do I do that?

I would probably start with the link offered to you in Post #7

 

[June7]

Review https://www.access-programmers.co.uk/forums/threads/conditional-formatting-using-vba.301613/