Change cell background color based on cell's text in excel using C# EPPlus

Question

I am trying to change cell's background color based on its text content. Eg: for text = "In progress" it should be Yellow, for text ="Completed" it should be Green. Not getting any clue to proceed with automation using C#.

Referred the below links but no luck

https://stackoverflow.com/questions/39484426/set-conditional-background-color-of-cell-based-on-text-using-epplus-in-c-net https://stackoverflow.com/questions/52737955/epplus-conditional-formatting

ExcelAddress _formatRangeAddress = new ExcelAddress("C2:C5");

var conditionalFormattingRule01 = workSheet.ConditionalFormatting.AddExpression(_formatRangeAddress);
conditionalFormattingRule01.Formula = "($C3=In Progress)";
conditionalFormattingRule01.Style.Fill.PatternType = ExcelFillStyle.Solid;
conditionalFormattingRule01.Style.Fill.BackgroundColor.Color = Color.Yellow;

Color should be changed to Yellow for In progress text in the cell

Answer 1

Here's a couple of different ways of achieving it.

With a formula expression, you need to use the format C2="In Progress", where C2 is the top cell in the range in which the conditional formatting applies - it will still apply to other cells in the range in the right way.

var formatExpressionInProgress = worksheet.ConditionalFormatting.AddExpression(new ExcelAddress("C2:C5"));
formatExpressionInProgress.Formula = "C2=\"In Progress\"";
formatExpressionInProgress.Style.Fill.PatternType = ExcelFillStyle.Solid;
formatExpressionInProgress.Style.Fill.BackgroundColor.Color = Color.Yellow;

More intuitively you could use the Equal type expression instead by using .AddEqual instead of AddExpression, then you just have "Completed" in the formula to match where the condition should apply.

var formatExpressionCompleted = worksheet.ConditionalFormatting.AddEqual(new ExcelAddress("C2:C5"));
formatExpressionCompleted.Formula = "\"Completed\"";
formatExpressionCompleted.Style.Fill.PatternType = ExcelFillStyle.Solid;
formatExpressionCompleted.Style.Fill.BackgroundColor.Color = Color.Green;