0

I am using Epplus For Export to Excel.

i want to set the background color of column 1 and 2 based on column 1 value. if any cell in column 2 cells contain 1 then the background color of col1 and col2 is of Green. if it contains 2 then the background Color must be light yellow. Like below Image.

Now I am able to set only 2nd column background color. if i set the range then it set the background color based on the Last condition and color the entire column as yellow. Please help me out. enter image description here

Richa
  • 11
  • 4

2 Answers2

0

Select all the data in Col1 and Col2. Go to the conditional formatting menu and click on manage rules. Choose the last option in the list (Use a formula to determine...) and use this formula: =if($[col2][row1ofData]=1,true,false). Then format as you wish. Apply the rule to Col1 and Col2.

The dollar sign will tell it to look at the value in Col2, despite applying the rule to Col1, for any row in the range.

You will then have to repeat this for every colour code you want to use (i.e. do it once to colour 1 as green and again to colour 2 as amber).

a-burge
  • 1,535
  • 1
  • 13
  • 25
  • I want to do this using EPPlUS not in MS Excel – Richa Sep 14 '16 at 10:04
  • Then I suggest you clarify your question as it is very difficult to know what software you need help with. Note that you have tagged your question with excel, hence my answer. – a-burge Sep 14 '16 at 10:14
0

I have found my own solution. below is excel outputenter image description here. 

 int Tocolumn = ws.Dimension.End.Column;

  foreach (ExcelRangeBase cell in ws.Cells[2, 1, ToRow, 2])
    {
        if (string.IsNullOrEmpty(cell.Text)) continue;
        var text = cell.Text;

        if (text.Equals("0"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#7fcbfe");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("1"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#90ee90");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("2"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#ffee75");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("3"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#fdb957");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("4"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#FF9985");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("5"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#33CCCC");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("6"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#66CCFF");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
        else if (text.Equals("7"))
        {
            Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF99");
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells[cell.Start.Row, cell.Start.Column - 1, cell.Start.Row, cell.Start.Column].Style.Fill.BackgroundColor.SetColor(colFromHex);
        }
    }
Richa
  • 11
  • 4