Conditional Formatting through VBA

[Jonmo1]

This is strange,

Sub Macro1()
    With Range("A1")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=A1=B1"
    .FormatConditions(1).Interior.ColorIndex = 3
    .FormatConditions.Add Type:=xlExpression, Formula1:="=A1<>B1"
    .FormatConditions(2).Interior.ColorIndex = 50
    .AutoFill Destination:=Range("A1:A29"), Type:=xlFillDefault
    End With
End Sub

If my active cell is NOT A1, the formula entered into conditional formatting is off. The range references get all messed up. For example, if my active cell is Say D5

Condition 1 is
=IT65533=IU65533
Condition 2 is
=IT65533<>IU65533

But if I run the code with A1 as my active cell, it gets entered correctly as
Condition 1
=A1=B1
Condition 2
=A1<>B1

Why does this happen ?

Thanks...

 

[pgc01]

If you turn on R1C1 references in the workbook, and record a macro, it records it the exact same way, except is using R1C1 style references:

Now, that was a great idea!!!

Why didn't I think of that?

I'm completely convinced. I've been using the R1C1 notation with the ConvertFormula method but this way is much better.

Cheers!

 

[Jonmo1]

Thanks for all the input. I am enlightened....

 

[Fazza]

I don't know about any gotchas. FWIW, for VBA setting of conditional formats, I've used the R1C1 references for some years and never NOTICED any problems.

Regards, Fazza

 

[Norie]

Bit late coming to the party.

But here's what I found out some time ago.

If you ran the code, with A1 notation, again you got the correct references - go figure.

If you use R1C1 for the formula it always seems to work.

Don't quote me on that, I mean it always seems to work when I've tried it.