Conditional Format in Report Using VBA

[Guest]

I am trying to format a text box in a report without using the Conditional
Format feature due to the number of values exceeding three. I basically have
a range of 0 - 100%. Using five different forecolors, I want to represent
different ranges. Here is my example:

Select Case Me.Score
Case Is > 0.6
Me.Score.ForeColor = 32768
Case Is < 0.61
Me.Score.ForeColor = 128
Case Is < 0.45
Me.Score.ForeColor = 8404992
Case Is < 0.32
Me.Score.ForeColor = 33023
Case Is < 0.2
Me.Score.ForeColor = 255
End Select

I have even tried an If statement:
If Me.Score > 0.6 Then
Me.Score.ForeColor = 32768
ElseIf Me.Score.Value > 0.44 < 0.61 Then
Me.Score.ForeColor = 128
ElseIf Me.Score.Value > 0.3 < 0.45 Then
Me.Score.ForeColor = 8404992
ElseIf Me.Score.Value > 0.18 < 0.31 Then
Me.Score.ForeColor = 33023
ElseIf Me.Score.Value < 0.19 Then
Me.Score.ForeColor = 255
End If

Any suggestions would be greatly appreciated!

 

[Guest]

I forgot to mention the in the Select Case example, only the first two cases
work. After that the rest of the numbers are not affected.

 

[Jörg]

The rest can´t work,

because if you have a value like 0.25 the second Case works
and if one case works, the rest dosn´t work any more

Other way:

0.25 is lower than 0.61
0.25 is lower than 0.45
0.25 is lower than 0.32

which of these 3 Cases should work now?? which color do you want to have ;-)


you´ve to need the following ranges:

=0.6

<0.6 AND >= 0.45
<0.45 AND >= 0.32
<0.32 AND >= 0.2
<0.2

then i think it works

Best regards

 

[Guest]

I tried using the example provided, however, the first challenge is the "AND"
in you statement does no work in a Select statement. Second, when deleting
the and just using the following:

Select Case Me.Score
Case Is >= 0.6
Me.Score.ForeColor = 32768
Case Is < 0.6 >= 0.45
Me.Score.ForeColor = 128
Case Is < 0.45 >= 0.32
Me.Score.ForeColor = 8404992
Case Is < 0.32 >= 0.2
Me.Score.ForeColor = 33023
Case Is < 0.2
Me.Score.ForeColor = 255
End Select

The first two cases are only working. The rest of values are set to the
second case.

Any other ideas????

 

[Jörg]

Hi Rob,

trying again using this code:
---------------------
Private Sub Detailbereich_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.Score
Case Is >= 0.6
Me.Score.ForeColor = 32768
Case 0.45 To 0.59999999
Me.Score.ForeColor = 128
Case 0.32 To 0.44999999
Me.Score.ForeColor = 8404992
Case 0.2 To 0.31999999
Me.Score.ForeColor = 33023
Case Is < 0.2
Me.Score.ForeColor = 255
End Select
End Sub

 

[Robin]

There might be a cleaner solution but I'm sure the following will work. It
steps through each level until it finds something untrue:

Me.Score.ForeColor = 255
If Me.Score.Value > 0.18 Then Me.Score.ForeColor = 33023
If Me.Score.Value > 0.3 Then Me.Score.ForeColor = 8404992
If Me.Score.Value > 0.44 Then Me.Score.ForeColor = 128
If Me.Score > 0.6 Then Me.Score.ForeColor = 32768

 

[Guest]

The trick with the select statement is that it stops at the first True value
it finds. If you put them in this order, it should work just fine except if
you hit a value that is from .6 to .609
Case Is < 0.61 will be false
Case Is > 0.6 will also be false

The solution is to change this line:
Case Is > 0.6
To
Case Is >= 0.6
Or
Case Else
The Case Else will get the color for any value .61 or higher

Select Case Me.Score
Case Is < 0.2
Me.Score.ForeColor = 255
Case Is < 0.32
Me.Score.ForeColor = 33023
Case Is < 0.45
Me.Score.ForeColor = 8404992
Case Is < 0.61
Me.Score.ForeColor = 128
Case Is > 0.6
Me.Score.ForeColor = 32768
End Select

 

[Guest]

Jörg,
It worked!!! Thanks this really helped.

Hi Rob,

trying again using this code:
---------------------
Private Sub Detailbereich_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.Score
Case Is >= 0.6
Me.Score.ForeColor = 32768
Case 0.45 To 0.59999999
Me.Score.ForeColor = 128
Case 0.32 To 0.44999999
Me.Score.ForeColor = 8404992
Case 0.2 To 0.31999999
Me.Score.ForeColor = 33023
Case Is < 0.2
Me.Score.ForeColor = 255
End Select
End Sub

 

[Guest]

Robin,
Yours worked as well. Thanks again.

There might be a cleaner solution but I'm sure the following will work. It
steps through each level until it finds something untrue:

Me.Score.ForeColor = 255
If Me.Score.Value > 0.18 Then Me.Score.ForeColor = 33023
If Me.Score.Value > 0.3 Then Me.Score.ForeColor = 8404992
If Me.Score.Value > 0.44 Then Me.Score.ForeColor = 128
If Me.Score > 0.6 Then Me.Score.ForeColor = 32768

 

[Jörg]

Hey Rob,

no problem...

Best regards...

Jörg,
It worked!!! Thanks this really helped.