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]

Hi Jonmo

You are using relative addresses, and conditional formatting considers it relative to the active cell.

Since you are in cell D5 you could use "=D5=E5" and this would be translated in A1 to "=A1=B1".

Since you used "=A1=B1" in D5, excel translates it to "the cell 3 columns to the left and 4 rows up, equal to, the cell 2 columns to the left and 4 rows up".

If you then apply it to A1, "the cell 3 columns to the left and 4 rows up" means that, since A1 is the top left cell, excel has to wrap around to the last columns rows.

Consider the rows/columns as wrapping around:

Columns IS, IT, IU, IV, A, B
Rows 65532, 65533, 65534, 65535, 65536, 1, 2

"the cell 3 columns to the left and 4 rows up" from A1, is IT65533
"the cell 2 columns to the left and 4 rows up" from A1, is IU65533

2 solutions:

1 - (I don't like) refer to the active cell, in this case "=D5=E5" to get "=A1=B1" in cell A1.

2 - I usually use R1C1 notation.

    .FormatConditions.Add Type:=xlExpression, _
          Formula1:=Application.ConvertFormula("=RC=RC[1]", xlR1C1, xlA1)

Hope it was clear.

 

[Jonmo1]

Yes, that's clear thanks. Any Idea Why it works like that? Why does conditional formatting in VBA refer to the Active Cell? It just doesn't make sense...

 

[pgc01]

Any Idea Why it works like that? Why does conditional formatting in VBA refer to the Active Cell? It just doesn't make sense...

No, I only found out the first time I used it and it didn't work. I used the formula just like you did, and after that it was trial and error. I tried afterwards to find some logic or some article on the web that would explain it but had no luck. I found code that uses this knowledge, but no explanation about the why it is like this.

Cheers

 

[RoryA]

It's a bug - see this MSKB article.

 

[Scott Huish]

This uses R1C1 notation and you can apply the Conditional Formatting to the entire range at once:

Sub Macro1()
    With Range("A1:A29").FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=RC1=RC2"
        .Item(1).Interior.ColorIndex = 3
        .Add Type:=xlExpression, Formula1:="TRUE"
        .Item(2).Interior.ColorIndex = 50
    End With
End Sub

 

[pgc01]

Rory:

Great, I hadn't found yet any article about this. I must say, however, that the workarounds that ms suggests seem a bit lame.

The first doesn't really solve the problem, with absolute addresses you can't copy the formats.

The second doesn't also seem a workaround, it's not "there's an error, this is how we work around it", it's ""there's an error, let's go along with it".

Hotpepper:

I agree with you, it had worked in the tests I made. The reason I didn't post it and will not use it is that I've not yet seen any ms example using the R1C1 notation directly. Until I do I'm afraid it might not work in some case, like with the .FormulaArray property that usually works with A1 but, sometimes, doesn't. Maybe it's just me being too cautious.


Remark: I believe you meant:

.Add Type:=xlExpression, Formula1:="=RC=RC[1]"

Cheers

 

[RoryA]

I must say, however, that the workarounds that ms suggests seem a bit lame

I agree, and the fact that they don't suggest using R1C1 notation leads me to think there may well be a 'gotcha' in there too. I can't say I've tested it, but creating a named formula and referring to that in the CF might be an option instead.

 

[Scott Huish]

I think the difference there is that if you use A1 style references and it is working, you are getting away with something that Microsoft explicity says not to use:


From the help file for FormulaArray:

If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style (see the second example).

The help file for adding an expression to Conditional Formats doesn't specify what format a formula has to take.

From the help file for Add as it applies to the FormatConditions object

Formula1 Optional Variant. The value or expression associated with the conditional format. Can be a constant value, a string value, a cell reference, or a formula.

 

[Scott Huish]

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:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/30/2008
'
'
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=RC1=RC2"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
End Sub