Access VBA to Change colour of cells in Report

[Owain]

Hi,

I'm trying to create a report within Microsoft Access 2000 and I would like to change the background colour of a box within a report based on the value returned.

The box is named "Progress_Percentage_Complete". Values in this box can range from 0 to 100. I am wanting the background colour to change as follows.

If value is 0 to 39 = red
40 to 59 = orange
60 to 79 = yellow
80 to 99 = light green
100= dark green

I gather that this can be done in VBA. However, I have very little experience in this area and wondered if anyone knows what code I would use to achieve the colour coding as detailed above. Also, where would I place this code within the report. Will it be found somewhere in the 'properties' of the Progree_Percentage_Complete Box?

Any help you can offer will be greatly appreciated!

Many thanks,

Owain.

 

[Norie]

Owain

Could you not use Conditional Formatting?

It's available in Access 2000 but I'm afraid it's limited, like Excel, to 3 conditions.

 

[Owain]

Hi Norie - I'm afraid I need 5 different colours so conditional formatting doesn't do it for me.

Thanks for the suggestion though.

Cheers,

Owain.

 

[SydneyGeek]

In your report, go to the Properties for Progress_Percentage_Complete and make sure in the Format tab that Back Style is Normal
Now select the properties of the Detail section, and in the Events tab, double-click the blank On Format line. You'll see Event Procedure. Click the Builder (...) button to go to the code module, and paste this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case [Progress_Percentage_Complete]
    Case Is < 40
        [Progress_Percentage_Complete].BackColor = 255 'red
    Case Is < 60
        [Progress_Percentage_Complete].BackColor = 52479 'orange
    Case Is < 80
        [Progress_Percentage_Complete].BackColor = 65535 'yellow
    Case Is < 100
        [Progress_Percentage_Complete].BackColor = 65280 'light green
    Case 100
        [Progress_Percentage_Complete].BackColor = 32896 'dark green
End Select
End Sub

Save the report and run it.

Denis

 

[Owain]

That has worked a treat - many thanks SydneyGeek.

Cheers,

Owain.

 

[afs24]

I'm trying to do something very similar to what the below code is doing expect that I need to look up something 1st. Basically, if WorkFuntion in my report equals CARTPICK then I want to follow the same conditional formating below. If Workfunction does not equal CARTPICK then I want my conditional formating to follow a different number range (i.e. <20 green, <40 blue, <60 black). Does this make sense?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case [Progress_Percentage_Complete]
Case Is < 40
[Progress_Percentage_Complete].BackColor = 255 'red
Case Is < 60
[Progress_Percentage_Complete].BackColor = 52479 'orange
Case Is < 80
[Progress_Percentage_Complete].BackColor = 65535 'yellow
Case Is < 100
[Progress_Percentage_Complete].BackColor = 65280 'light green
Case 100
[Progress_Percentage_Complete].BackColor = 32896 'dark green
End Select
End Sub

 

[SydneyGeek]

Hi afs, you could use a nested Case statement. Like...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case [WorkFunction]
    Case "CARTPICK"
        Select Case [Progress_Percentage_Complete]
            Case Is < 40
                [Progress_Percentage_Complete].BackColor = 255 'red
            Case Is < 60
                [Progress_Percentage_Complete].BackColor = 52479 'orange
            Case Is < 80
                [Progress_Percentage_Complete].BackColor = 65535 'yellow
            Case Is < 100
                [Progress_Percentage_Complete].BackColor = 65280 'light green
            Case 100
                [Progress_Percentage_Complete].BackColor = 32896 'dark green
        End Select
    Case Else
        Select Case [Progress_Percentage_Complete]
            Case Is < 20
                [Progress_Percentage_Complete].BackColor = 255 'red
            Case Is < 40
                [Progress_Percentage_Complete].BackColor = 52479 'orange
            Case Is < 60
                [Progress_Percentage_Complete].BackColor = 65535 'yellow
            Case Is < 100
                [Progress_Percentage_Complete].BackColor = 65280 'light green
            Case 100
                [Progress_Percentage_Complete].BackColor = 32896 'dark green
        End Select
End Select
End Sub

You'll need to insert the new color codes for the second set. I got the values by picking colours for a textbox, then checking the Backcolor property.

Denis

 

[afs24]

This code works perfectly. However, I actually have 5 different Workfunctions that may come up (i.e. CARTPICK, FULLPICK, PUTAWAY, RECEIVING, REPLENISHMENT). Each workfunction has a differnt productivity measurement. Can you provide me with an example with the one previously except that I want to look up 5 different workfuctions? thanks

 

[SydneyGeek]

The Select statement can be expanded as much as you need. This section...

    Case "CARTPICK"
        Select Case [Progress_Percentage_Complete]
            Case Is < 40
                [Progress_Percentage_Complete].BackColor = 255 'red
            Case Is < 60
                [Progress_Percentage_Complete].BackColor = 52479 'orange
            Case Is < 80
                [Progress_Percentage_Complete].BackColor = 65535 'yellow
            Case Is < 100
                [Progress_Percentage_Complete].BackColor = 65280 'light green
            Case 100
                [Progress_Percentage_Complete].BackColor = 32896 'dark green
        End Select

...can be repeated with copy and paste, just before the Case Else bit. Then go through, changing CARTPICK to one of the other four categories, and setting your cutoffs as required.

Denis

 

[orzik]

All,
I am new to Access macros...
I am trying to change color of values in one column of my report [Q1] ($ field) based on another column [StatusT] (text).

[StatusT] values are as follows:
-Red
-Green
-Blue
-Black

[Q1] values: any $ currency value

Sample Output should be:
If [StatusT] = Red then value in [Q1] is painted Red

After careful analysis of above macros this is what I came up with for my case.
As a result I do not get any errors but it does not colour my values either.
Please help,

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case [StatusT]
   Case Is = Red
        [Q1].BackColor = 255 'red
    Case Is = Blue
        [Q1].BackColor = 16711680 'blue
    Case Is = Black
        [Q1].BackColor = 0 'black
    Case Is = Green
        [Q1].BackColor = 32768 'green
End Select
End Sub