title: EPPLUS multiple columns configuration and conditional formatting in Excel C#
body: <p>In my tool, user can choose one configuration (through combobox->multiple datatable) and the respective table will reflect in the excel sheet as per below.Columns (data in rows will differ) that will remain the same for all configuration are Product Name, Serial Name and Length 1 and Total Length. Different configuration will have added columns such as Length 2, Length 3,Length 4 (user will add the data in these rows)etc.</p>
  
  <p>I want to add conditional formatting formula in Total Length column where background cell will turn green if it is in range (minval to maxval) and red when it is out of range. I am stuck with my code without solution.It did not change any color when the user add the data in the excel.  Help. Thanks!</p>
  
  <p>Table</p>
  
  <p><a href="https://i.stack.imgur.com/QpV7S.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QpV7S.jpg" alt="enter image description here"></a></p>
  
  <pre><code>     private void ManualFormatExcelandAddRules(ExcelWorksheet WS, DataTable DTtoFormat, int ColStartAddOfDT, int RowStartAddOfDT)
          {
              int colCountofDT = DTtoFormat.Columns.Count;
              int rowCountofDT = DTtoFormat.Rows.Count;
              double minval = 0;
              double maxval = 0;
              int flag = 0; 
              for (int Colno = ColStartAddOfDT; Colno &lt; ColStartAddOfDT + colCountofDT; Colno++)
              {
                  WS.Cells[RowStartAddOfDT, Colno].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                  for (int RowNo = RowStartAddOfDT + 1; RowNo &lt;= RowStartAddOfDT + rowCountofDT; RowNo++)
                  { if (WS.Cells[RowNo, Colno].Text.Contains("to") &amp;&amp; WS.Cells[RowNo, ColStartAddOfDT].Text.Contains("DRAM"))
                          {
                              string[] GuidelineVal = WS.Cells[RowNo, Colno].Text.Split("to".ToArray(), StringSplitOptions.RemoveEmptyEntries).ToArray();
                              if (GuidelineVal[0].Trim() != "NA" &amp;&amp; GuidelineVal[1].Trim() != "NA")
                              {
                                  minval = Convert.ToDouble(GuidelineVal[0].Trim());
                                  maxval = Convert.ToDouble(GuidelineVal[1].Trim());
                                  flag = 0;
                              }
                              else
                                  flag = 1;
                          }
  
                          else if (WS.Cells[RowNo, Colno].Text == "" &amp;&amp; WS.Cells[RowStartAddOfDT + 1, Colno].Text.Contains("to"))
                          {
  
  
                              if (flag == 0)
                              {
  
                                  string _statement = "AND(Convert.ToDouble(WS.Cells[RowNo, Colno].Text) &gt;= minval,Convert.ToDouble(WS.Cells[RowNo, Colno].Text) &lt;= maxval)";
                                  var _cond = WS.ConditionalFormatting.AddExpression(WS.Cells[RowNo, Colno]);
                                  _cond.Formula = _statement;
                                  _cond.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                  _cond.Style.Fill.BackgroundColor.Color = Color.Green;                              
  
                              }
                              else
                                  WS.Cells[RowNo, Colno].Style.Fill.BackgroundColor.SetColor(Color.Red);
                                  WS.Cells[RowNo, Colno].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                      }
     }
  }
  </code></pre>