Change rows color

-1

I have an excel file and i need to check the column B for a certain range (for example range: 1:3000) and if is present a value different from "new" and empty cell so i need to change the row color in grey from column B to O. Like this: enter image description here

How can i do this in C#? If necessary I prefer Microsoft.Interop.Excel to others... I already check similar answers and at last i tried something like this but nothing happens... what is the simplest solution?

using Excel=Microsoft.Office.Interop.Excel;
using System;
using Microsoft.Office.Interop.Excel;

    namespace RowsColorChange

{
    class Program
    {
        static void Main(string[] args)
        {
          
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\Something\\Ge\\Output\\2020-06 1 - MR - Pronto - Copia.xlsm", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        FormatCondition format = (FormatCondition)(xlWorkSheet.get_Range("B3:B2000",
            Type.Missing).FormatConditions.Add(XlFormatConditionType.xlExpression, XlFormatConditionOperator.xlEqual,
            "=$B3<>null","=$B3<>"+"new", Type.Missing, Type.Missing, Type.Missing, Type.Missing));
        format.Font.Bold = true;
        format.Font.Color = 0x000000FF;
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
c#
excel-interop
xlsm

1 Answer

1

Use Conditional Formatting.

  • Select the B though O column headers to select the all of the columns, or if you prefer, select B1:O3000 (PS a quick way to do this is to click in the Name Box to the left of the formula bar and type in B1:O3000).

  • Select Conditional Formatting | New Rule... from the Styles group on the Home ribbon.

  • Select Use a formula to determine which cells to format from the Select a Rule Type box.

  • In the Format values where this formula is true: input field, type in =AND($B1<>"new",$B1<>"")

  • Select Format... and on the Fill tab set the fill you want to use and select OK.

  • Select OK again to close the New Formatting Rule dialog.

answered on Stack Overflow Aug 5, 2020 by NetMage

User contributions licensed under CC BY-SA 3.0