CODEWITHSUNDEEP
vbams-accessms-access-2010
sigil
sigil

Reputation: 9546

Applying conditional formatting in unusual table situation

I have a datasheet form based on the following query, which is a union of several data sets that have been pivoted to give columns by date:

+-------------+------------------+----------+----------+-----------+
| statusType  |    valueType     | 8/1/2013 | 9/1/2013 | 10/1/2013 |
+-------------+------------------+----------+----------+-----------+
| design      | actual           |        3 |        6 |         7 |
| design      | target           |        4 |        5 |         4 |
| design      | cumulativeActual |       60 |       66 |        67 |
| design      | cumulativeTarget |       50 |       55 |        54 |
| development | actual           |       10 |       12 |         2 |
| development | target           |       10 |        8 |         8 |
| development | cumulativeActual |       30 |       42 |        44 |
| development | cumulativeTarget |       40 |       48 |        56 |
+-------------+------------------+----------+----------+-----------+  ...

I want to set conditional formatting on the "actual" values in each date column so that colors are set for each value as follows:

So, e.g., the "design actual" value for 8/1 would be blue, and the "development actual" values for 9/1 and 10/1 would be green and red respectively.

I can iterate through the date controls setting their conditional formatting as follows:

Private Sub Form_Load()

Dim ctrl As Control
Dim tb As TextBox


For Each ctrl In Me.Controls
    If IsDate(ctrl.Name) Then
        Set tb = ctrl
        tb.FormatConditions.Add 'not sure what to put here
    End If
Next

End Sub

I don't understand what I should put in the arguments for FormatConditions.Add here; for a given statusType/actual/date value, how would I fetch the corresponding statusType/target/date value so as to compare them for setting the formatting?

Note that this form is read-only, so maybe it doesn't have to be conditional--maybe I could just assign static colors to the values according to the above rules whenever the form is loaded.

ADDITIONAL EXPLANATION: What I'm trying to do, basically, is color values in one row according to how they compare to values in the same column in another row. E.g. with the "8/1/2013" column, comparing the "design|actual" value of 3 with the "design|target" value of 4 to determine what color the "design|actual" value should be.

Upvotes: 0

Views: 108

Answers (1)

HK1
HK1

Reputation: 12210

I guess I don't quite understand what you're trying to do.

If you need different conditional formatting configured for each row individually, I don't think that's going to work. If you cannot setup your conditional formatting in Design View then you probably won't get it to work using code either.

In case you still want some code, here's how you do conditional formatting in code using Expressions:

Private Sub Form_Load()

    Dim ctrl As Control
    Dim tb As TextBox
    Dim fc as FormatCondition

    For Each ctrl In Me.Controls
        If IsDate(ctrl.Name) Then
            Set tb = ctrl
            Set fc = tb.FormatConditions.Add(acExpression, , "Me![FieldName] = ""Value""")
            fc.BackColor = 13611711
        End If
    Next

End Sub

Upvotes: 0

Related Questions