Excel Interop Conditional Formatting

Question

I just noticed the following page: Setting up conditional formatting in Excel 2007 , which is very similar to what I would like to do, but I cannot find suitable functions to do something a little different.

I am wondering if anyone knows a way to apply conditional formatting to a range based on a set of text values. For instance. I want to say:

If you see "InvalidValue1" OR "InvalidValue2", highlight RED otherwise, if you see "WARNING" Highlight YELLOW

I have a number of invalid values ​​and possibly warning values. I also need to do this column by column for very large datasets, so if possible, I would like to use Excel's built-in functions to highlight errors within a range.

Does anyone know if this is possible?

Hello

Answer 1

I believe that I was able to find a solution to the problem (although Cell's choice is rather strange, and I have not quite figured it out yet, for example, my formula uses A1, which actually means C1 because of the selected range).

Here is the code I used for someone else:

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 Excel interaction.

Answer 2

  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; 

Answer 3

If you are using .Net 4, rewrite using the 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;