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:
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);
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.
User contributions licensed under CC BY-SA 3.0