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.

 

[SydneyGeek]

Very close, but Red is text so you have to tell Access that, using quotes. Try this:

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

Denis

 

[orzik]

Thanks Denis! that did it.
I also had to make sure that the "Back Style" property was set to "Normal".

Thanks,