Iterating through Conditional Format Formulas not working as expected

[Dirkasaurus]

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!

 

[gregtx81]

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]

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]

Dirk,

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