MrExcel Consulting
Your One Stop for Excel Tips & Solutions

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