The Elusive Formula1 Property For Conditional Formatting

Category: VBA Weirdness | [Item URL]

Excel's Conditional Formatting feature is useful, but one gets the impression that the designers were forced to implement it in a hurry. It suffers from two serious problems:

1. Copying and pasting a cell over a cell that uses conditional formatting wipes out the formatting rules. Doh!
2. There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered."

In an effort to solve #2, I created a custom VBA function. But then I discovered something very strange:

When a Conditional Formatting formula uses a relative range reference, accessing the Formula1 property via VBA will give you a different formula, depending on the active cell position!

To demonstrate... Activate cell A1 and add this conditional formatting rule: formula is =B1>0

With cell A1 activated, execute this sub:

Sub Test1() 
    Dim F1 As String 
    F1 = Range("A1").FormatConditions(1).Formula1 
    MsgBox F1 
End Sub 

You'll see the correct formula.

Now, activate any other cell and re-run the procedure. The Formula1 property will return a different formula, which is relative to the active cell.

In order to retrieve the actual Formula1 property value, you need to convert the formula to R1C1 notation using the active cells as the reference. Then convert that R1C1 formula back to A1 style. The procedure below returns the actual Formula1 value, regardless of the active cell position.

Sub Test2() 
    Dim F1 As String, F2 As String 
    F1 = Range("A1").FormatConditions(1).Formula1 
    F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell) 
    F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Range("A1")) 
    MsgBox F1 
End Sub