Conditional format in VBA
Posted by Govindaraj Rangan on December 20, 2001 5:05 AM
Hi,
I've set conditional formatting on a range. How do I find from a VBA module whether that condition was true or false for a given cell? Is conditional format stored as a property of the cell?
Mine is a very urgent requirement and any help on this highly appreciated.
Regards,
Govind.
Posted by Alcibiades on December 20, 2001 6:14 AM
Use the same condition in your code.
For example :-
If Range("A1").Value >1 Then
MsgBox "Condition is true"
End if
Posted by sandra on December 20, 2001 7:28 AM
You can use the following macro to check whether the value of the active cell met the defined conditional format.
The macro can only check the conditional format of the type "Cell value is", maybe somebody else can also check for type "Formula is"
Sub CheckFormat()
iFalse = 0
If ActiveCell.FormatConditions.Count = 0 Then
MsgBox " No conditional format is defined for this cell"
Else
For i = 1 To ActiveCell.FormatConditions.Count
If ActiveCell.FormatConditions.Item(i).Type = 1 Then
bCheck = False
Select Case ActiveCell.FormatConditions.Item(i).Operator
Case 1 ' between
If ActiveCell.Value >= ActiveCell.FormatConditions.Item(i).Formula1 And _
ActiveCell.Value <= ActiveCell.FormatConditions.Item(i).Formula2 Then _
bCheck = True
Case 2 ' not between
If ActiveCell.Value < ActiveCell.FormatConditions.Item(i).Formula1 Or _
ActiveCell.Value > ActiveCell.FormatConditions.Item(i).Formula2 Then _
bCheck = True
Case 3 ' equal to
If ActiveCell.Value = ActiveCell.FormatConditions.Item(i).Formula1 Then _
bCheck = True
Case 4 ' not equal to
If ActiveCell.Value <> ActiveCell.FormatConditions.Item(i).Formula1 Then _
bCheck = True
Case 5 ' greater then
If ActiveCell.Value > ActiveCell.FormatConditions.Item(i).Formula1 Then _
bCheck = True
Case 6 ' less then
If ActiveCell.Value < ActiveCell.FormatConditions.Item(i).Formula1 Then _
bCheck = True
Case 7 ' greater & equal then
If ActiveCell.Value >= ActiveCell.FormatConditions.Item(i).Formula1 Then _
bCheck = True
Case 8 ' less & equal then
If ActiveCell.Value <= ActiveCell.FormatConditions.Item(i).Formula1 Then _
bCheck = True
End Select
If bCheck = True Then
MsgBox ActiveCell.Address & " met conditional format nr " & i
Else
iFalse = iFalse + 1
End If
Else
MsgBox " Formula = " & ActiveCell.FormatConditions.Item(i).Formula1
End If
Next i
If iFalse = 3 Then MsgBox " No conditional format met for this cell "
End If
End Sub
