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
Excel is a complex program, and has been around for a long time. Consequently, it has many obscure nooks and crannies to discover. Some of them are described here.
Keep in mind that some versions of Excel are odder than others. In other words, the things described here may not apply to all versions of Excel.
All Odd Stuff
Browse Oddities by Category
Seen Something Odd?
If you've discovered something weird about Excel, let me know.