How do you get ClosedXML to use conditional formatting with formulas?

Question

According to the documentation you can add conditional formatting to a cell using the syntax:

.AddConditionalFormat().WhenEquals("=B1")

So I tried this:

cell.AddConditionalFormat().WhenEquals("=F5=0")
    .Fill.SetBackgroundColor(XLColor.FromHtml("#f00"));

However, whenever I try this, load the spreadsheet into Excel, and look at the conditional formatting for the cell, it seems to have changed it to a simple 'cell value equals' type, rather than a formula type. So I see this:

but what I want to see is this:

What am I missing!?

Illaffected

Answer 1

After fruitlessly digging in Google for hours, I gave up and went back to basics. I eventually noticed a method called WhenIsTrue(), which accepts a formula!

So, in case anyone else gets here looking for this (this question seems to be the second best ranking answer in Google for 'conditional formatting closedxml'), this is the answer.

cell.AddConditionalFormat().WhenIsTrue("=F5=0")
    .Fill.SetBackgroundColor(XLColor.FromHtml("#f00"));

Illaffected

Answer 2

I was able to get this working like this:

.AddConditionalFormat().WhenEquals("=$B1")

Hanaper