I have applied formatting conditions to a range of cells on the spreadsheet as shown in the image.
I have VBA code which evaluates format conditions and outputs based on them.
The problem is that when the conditions are applied in this way the VBA code pulls the formatting based on the first cell in the range and thus it evaluates incorrectly. Is there a VBA workaround for this? I need to keep the formatting on the spreadsheet the same.
Example code:
I have VBA code which evaluates format conditions and outputs based on them.
The problem is that when the conditions are applied in this way the VBA code pulls the formatting based on the first cell in the range and thus it evaluates incorrectly. Is there a VBA workaround for this? I need to keep the formatting on the spreadsheet the same.
Example code:
VBA Code:
Private Function ConditionNo(ByVal rgeCell As Range) As Integer
On Error GoTo ErrHandler
Dim iconditionscount As Integer
Dim objFormatCondition As FormatCondition
For iconditionscount = 1 To rgeCell.FormatConditions.count
Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
If objFormatCondition.Type = xlExpression Then
If Application.Evaluate(objFormatCondition.Formula1) Then
ConditionNo = iconditionscount
Exit Function
End If
ElseIf InStr(1, objFormatCondition.Formula1, "ISBLANK") = 2 Then
If IsEmpty(rgeCell.value) Then
ConditionNo = iconditionscount
Exit Function
End If
ElseIf objFormatCondition.Type = xlCellValue Then
Select Case objFormatCondition.Operator
Case xlBetween: If Compare(rgeCell.value, ">=", objFormatCondition.Formula1) = True And _
Compare(rgeCell.value, "<=", objFormatCondition.Formula2) = True Then _
ConditionNo = iconditionscount
Case xlNotBetween: If Compare(rgeCell.value, "<", objFormatCondition.Formula1) = True Or _
Compare(rgeCell.value, ">", objFormatCondition.Formula2) = True Then _
ConditionNo = iconditionscount
Case xlGreater: If Compare(rgeCell.value, ">", objFormatCondition.Formula1) = True Then _
ConditionNo = iconditionscount
Case xlEqual: If Compare(rgeCell.value, "=", objFormatCondition.Formula1) = True Then _
ConditionNo = iconditionscount
Case xlGreaterEqual: If Compare(rgeCell.value, ">=", objFormatCondition.Formula1) = True Then _
ConditionNo = iconditionscount
Case xlLess: If Compare(rgeCell.value, "<", objFormatCondition.Formula1) = True Then _
ConditionNo = iconditionscount
Case xlLessEqual: If Compare(rgeCell.value, "<=", objFormatCondition.Formula1) = True Then _
ConditionNo = iconditionscount
Case xlNotEqual: If Compare(rgeCell.value, "<>", objFormatCondition.Formula1) = True Then _
ConditionNo = iconditionscount
End Select
If ConditionNo > 0 Then Exit Function
Else
Debug.Print "Could not detect cell formula"
End If
Next iconditionscount
Exit Function
ErrHandler:
MsgBox "[System Error] in ConditionalFormatting.ConditionNo: " + Err.Description
ConditionNo = -99
End Function
Attachments
Last edited by a moderator: