我正在尝试根据单元格的内容更改其背景颜色。我们正在查看成绩,无论是"A 到 U"还是"9 到 1"。如果成绩比目标成绩低 2 分,则需要突出显示该单元格。如果单元格只是数字或字母,但不能两者兼而有之,我们就可以工作。
以下是报告的自定义代码:
Public Function gradeCheck(SubjectGrade AS String, TargetGrade AS String) AS String
Dim gradeValue = convertGradeToInt(SubjectGrade)
Dim targetValue = convertGradeToInt(TargetGrade)
If (targetValue - gradeValue) < -1
Return "Orange"
Else
Return "White"
End If
End Function
Public Function gradeCheckInt(SubjectGradeInt AS Integer, TargetGradeInt AS Integer) AS String
If (TargetGradeInt - SubjectGradeInt) >= 2
Return "Orange"
Else
Return "White"
End If
End Function
Public Function convertGradeToInt(grade AS String) AS Integer
Select Case grade
Case "A*"
return 1
Case "A"
return 2
Case "B"
return 3
Case "C"
return 4
Case "D"
return 5
Case "E"
return 6
Case "F"
return 7
Case "G"
return 8
Case "U"
return 9
End Select
End Function
以下是适用于数字本身或字母本身的代码:
函件 = IIF(ISNOTHING(Fields!report_Grade.Value) 、"白色" , IIF(ISNOTHING(Fields!目标等级.价值) 、"白色" ,代码.gradeCheck(字段!report_Grade.值,字段!目标等级.价值) ) )
数字
= IIF(ISNOTHING(Fields!report_Grade.Value)
, "White"
, IIF(ISNOTHING(Fields!TargetGrade.Value)
, "White"
,code.gradeCheckInt(Fields!report_Grade.Value, Fields!TargetGrade.Value)
)
)
这是我认为应该有效的,但只突出显示正确的数字,而不是字母:
= IIF(ISNOTHING(Fields!report_Grade.Value)
, "White"
, IIF(ISNOTHING(Fields!TargetGrade.Value)
, "White"
,IIF(IsNumeric(Fields!report_Grade.Value)
,code.gradeCheckInt(Fields!report_Grade.Value, Fields!TargetGrade.Value)
,code.gradeCheck(Fields!report_Grade.Value, Fields!TargetGrade.Value)
)
)
)
任何帮助将不胜感激。
谢谢
看起来您的成绩总是来自同一列,因此这必须是一个字符串(即使它包含一个数字)。如果是这种情况,那么我会convertGradeToInt
通过测试字符串是否是类似
Public Function convertGradeToInt(grade AS String) AS Integer
If Val(grade) > 0 then
return Val(grade)
ELSE
Select Case grade
Case "A*"
return 1
Case "A"
return 2
Case "B"
return 3
Case "C"
return 4
Case "D"
return 5
Case "E"
return 6
Case "F"
return 7
Case "G"
return 8
Case "U"
return 9
End Select
End If
End Function
现在,如果传入的值是数字,则只需返回相同的值,如果它是一个字符串,则返回匹配的等级(如果未找到匹配项,则返回零)。
现在您的色彩表达可以很简单了
=IIF(Code.convertGradeToInt(Fields!TargetGrade.Value) - Code.convertGradeToInt(Fields!report_Grade.Value) >= 2 , "Orange", Nothing)
您不需要任何其他代码,只需要一个函数和一个颜色表达式。
我可能忽略了一些东西,因为它没有经过测试,但希望它能给你足够的时间来解决问题。
多亏了上面的艾伦,我才能让它工作。这是我使用的最终自定义代码:
Here is the final custom code I used:
Public Function gradeCheck(SubjectGrade AS Integer, TargetGrade AS Integer) AS String
If TargetGrade - SubjectGrade >= 2
Return "Orange"
Else
Return "White"
End If
End Function
Public Function convertGradeToInt(grade AS String) AS Integer
If Val(grade) > 0 then
return Val(grade)
ELSE
Select Case grade
Case "A*"
return 9
Case "A"
return 8
Case "B"
return 7
Case "C"
return 6
Case "D"
return 5
Case "E"
return 4
Case "F"
return 3
Case "G"
return 2
Case "U"
return 1
End Select
End If
End Function
这是颜色表达式:
= IIF(ISNOTHING(Fields!report_Grade.Value)
, "White"
, IIF(ISNOTHING(Fields!TargetGrade.Value)
, "White"
, code.gradeCheck(Code.convertGradeToInt(Fields!report_Grade.Value), Code.convertGradeToInt(Fields!TargetGrade.Value))
)
)