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!?

score 23 · Accepted Answer · answered Sep 15 '15 at 15:02

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"));

score 0 · Answer 2 · answered Feb 29 '16 at 16:50

0

I was able to get this working like this:

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

answered Feb 29 '16 at 16:50

mike

103
2
8

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

2 Answers2