Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



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.


Check out our Excel VBA Resources

Re: Conditional format in VBA

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


Re: Conditional format in VBA

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




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.