Hi,
I tried to build a reprot which change one of number fields color depend on another Category field at the report and number field itself.
If there is Category A, then number field will change color at certain number range.
If there is category B, then the number field will have different number range to change color.
Below is my code, however, it only works for one of Cases. Please help, thanks a lot.
I tried to build a reprot which change one of number fields color depend on another Category field at the report and number field itself.
If there is Category A, then number field will change color at certain number range.
If there is category B, then the number field will have different number range to change color.
Below is my code, however, it only works for one of Cases. Please help, thanks a lot.
Code:
Private Sub Report_Load()
' This subroutine demonstrates the use of FormatCondition objects
' to add formatting to a text box. It also illustrates ways you can
' extend the functionality of the three conditional-format limit
' allowed in the FormatConditions collection.
'
' Author: Frank C. Rice
'
Dim objFrc As FormatCondition
Dim lngRed As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim lngYellow As Long
' Set up background and foreground colors.
lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngGreen = RGB(34, 139, 34)
' Depending on the user's option selection, format the txtResult
' box.
Select Case Category.Value
Case "A"
' Refer to each format condition by its index.
' Remove any existing format conditions.
Me![rptHoursLeft].FormatConditions.Delete
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acLessThan, 0)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acBetween, 0, 25)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acGreaterThan, 25)
With Me![rptHoursLeft].FormatConditions(0)
.BackColor = lngRed
.FontBold = True
.ForeColor = lngBlack
End With
With Me![rptHoursLeft].FormatConditions(1)
.BackColor = lngYellow
.FontBold = True
.ForeColor = lngGreen
.FontUnderline = True
End With
With Me![rptHoursLeft].FormatConditions(2)
.FontBold = False
.FontItalic = False
.FontUnderline = False
End With
Case "B"
' Remove any existing format conditions.
Me![rptHoursLeft].FormatConditions.Delete
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acLessThan, 0)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acBetween, 0, 95)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acGreaterThan, 95)
With Me![rptHoursLeft].FormatConditions(0)
.BackColor = lngRed
.FontBold = True
.ForeColor = lngBlack
End With
With Me![rptHoursLeft].FormatConditions(1)
.BackColor = lngYellow
.FontBold = True
.ForeColor = lngGreen
.FontUnderline = True
End With
With Me![rptHoursLeft].FormatConditions(2)
.FontBold = False
.FontItalic = False
.FontUnderline = False
End With
Case "C"
' Remove any existing format conditions.
Me![rptHoursLeft].FormatConditions.Delete
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acLessThan, 0)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acBetween, 0, 5)
Set objFrc = Me![rptHoursLeft].FormatConditions.Add(acFieldValue, _
acGreaterThan, 5)
With Me![rptHoursLeft].FormatConditions(0)
.BackColor = lngRed
.FontBold = True
.ForeColor = lngBlack
End With
With Me![rptHoursLeft].FormatConditions(1)
.BackColor = lngYellow
.FontBold = True
.ForeColor = lngGreen
.FontUnderline = True
End With
With Me![rptHoursLeft].FormatConditions(2)
.FontBold = False
.FontItalic = False
.FontUnderline = False
End With
End Select
End Sub
Last edited by a moderator: