Iterating through Conditional Format Formulas not working as expected

Dirkasaurus

New Member
Joined
Aug 15, 2011
Messages
6
I read Bill's "VBA and Macros in Excel 2007" chapter on Conditional Formatting. That said, I'm finding that iterating through the ConditionalFormats.Formula1 collection to not work as expected.

For example, a cell shows in the User Interface the following three rules:
Rule 1: "=AND(LENB(B17)>0,UPPER($F$9)="STATIC")"
Rule 2: "=LENB($B17)=0"
Rule 3: "=UPPER($F$9)="DYNAMIC""

When I use this code, I get the following result:
Dim oCF as ConditionalFormat
Dim rng as Range

Set rng = Range("J17").selection
For Each oCF in rng.ConditionalFormats
debug.print oCF.Formula1
Next

The output in the Immediate Window is:
=LENB($B17)=0
=LENB($B17)=0
=LENB($B17)=0
============================================
To address this a different way, I then try this in the Immediate Window (with the active cell J17):
?Activecell.FormatConditions.count
3

?
?Activecell.FormatConditions(1).Formula1
=LENB($B17)=0
?Activecell.FormatConditions(2).Formula1
=LENB($B17)=0
?Activecell.FormatConditions(3).Formula1
=LENB($B17)=0
============================================

It does not make any difference the order of the rules in J17. Nor does it make any difference if I enter the rule via the UI or if I use VBA Code to place the rules in the cell using R1C1 notation (as recommended in Bill's book).

Does anyone have any comments about the short coming of iterating through the FormatConditions.Formula1 collection and correctly obtaining the formula that are associated with the rules?

Thanks!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

gregtx81

Board Regular
Joined
Feb 5, 2011
Messages
110
Code:
Set rng = Range("J17").selection

This causes an error-- set rng to J17 or to selection, but not both

Set rng = Range("J17")
Set rng = selection
 

Dirkasaurus

New Member
Joined
Aug 15, 2011
Messages
6
I agree that I wrote the set range statement incorrectly in the post. That aside, the issue is that Excel is not returning the correct formula1 values for the collection.

Does anyone have an idea of how to get Excel to return the correct formulas associated with the conditional formatting rules?

I've spent many hours trying to get this to work consistently and have had only intermittent success.

Thank you for your consideration...
 

gregtx81

Board Regular
Joined
Feb 5, 2011
Messages
110
Dirk,

The object is 'formatcondition' rather than 'conditionalformat'.
You might consider using Option Explicit to catch declaration errors.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,852
Messages
5,574,667
Members
412,610
Latest member
bluedusty
Top