1

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

Kevin Shah
  • 1,589
  • 1
  • 13
  • 20
b_J
  • 115
  • 10
  • The formula is the same whether you use EPPlus or Excel. Would `($C3=In Progress)` be valid in Excel? I suspect no, `In Progress` isn't quoted. You can use Excel to test the formula. You can also create a sheet in Excel that works the way you want and then *inspect it* to see how it works, either with Epplus itself or another tool. You could also use that sheet as a template instead of adding the formulas in code – Panagiotis Kanavos May 24 '19 at 15:20

1 Answers1

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;
steve16351
  • 5,372
  • 2
  • 16
  • 29