The elusive Formula1 property for conditional formatting
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:
- Copying and pasting a cell over a cell that uses conditional formatting wipes out the formatting rules. Doh!
- 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