Back to Excel VBA archive index

Back to archive home

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?

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 | ||||||

Use the same condition in your code.

For example :-

If Range("A1").Value >1 Then

MsgBox "Condition is true"

End if

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"

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.

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.