CODEWITHSUNDEEP
.netinteropms-office
Ian
Ian

Reputation: 34489

Excel Interop Conditional Formatting

I've just spotted the following page: Setting Conditional Formatting in Excel 2007 which is very similar to something I'd like to do, but I can't seem to find appropriate functions to do something slightly different.

I'm wondering if anyone knows a way to apply conditional formatting to a range, based upon a set of textual values. E.g. I want to say:

If you see "InvalidValue1" OR "InvalidValue2" Highlight RED else if you see "WARNING" Highlight YELLOW

I have a whole range of invalid values, and possibly warning values. I also need to do this on a column by column basis for very large datasets, so where possible I'd like to use built in Excel features to highlight errors within the range.

Does anyone know if this is at all possible?

Upvotes: 7

Views: 9571

Answers (3)

hojjat pakzad
hojjat pakzad

Reputation: 31

 using Excel = Microsoft.Office.Interop.Excel;
 ...
 object mis = Type.Missing;

 Excel.FormatCondition cond =
    (Excel.FormatCondition)range.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue,
    Excel.XlFormatConditionOperator.xlEqual, "1",
    mis, mis, mis, mis, mis);
    cond.Interior.PatternColorIndex = Excel.Constants.xlAutomatic;
    cond.Interior.TintAndShade = 0;
    cond.Interior.Color = ColorTranslator.ToWin32(Color.White);
    cond.StopIfTrue = false;

Upvotes: 3

John Oxley
John Oxley

Reputation: 14990

If you're using .Net 4, the following is a rewrite using dynamics and named parameters

dynamic range = sheet.Range("A2").Resize(rowCount, 11);

const string redCondition = "=OR(ERROR1, ERROR2, ERROR3)";

dynamic format = range.FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1: redCondition);
format.Interior.Color = 0x0000FF;
format.Font.Color = 0x00FFFF;

Upvotes: 2

Ian
Ian

Reputation: 34489

I believe I have managed to find a solution to the problem (although Cell selection is rather bizarre and I haven't quite sorted that out yet. e.g. my formula uses A1 which actually means C1 because of the selected range).

Here is the code I used for anyone else interested:

string condition = @"=OR(ERROR1, ERROR2, ERROR3)";
var cfOR = (FormatCondition)targetSheet.get_Range("C1", "C10").FormatConditions.Add(XlFormatConditionType.xlExpression, Type.Missing,condition), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

cfOR.Interior.Color = 0x000000FF;
cfOR.Font.Bold = true;
cfOR.Font.Color = 0x00FFFFFF;

Note that the FormatConditions.Add() method has a different signature for different versions of the Excel interop.

Upvotes: 9

Related Questions