How to create "Text Contains" FormattingConditional (Format Condition) for Excel with C#

Question

What I want is to be able to change the color of some Excel file's Cells based on the text value in them.

Here is what I have:

private void validator(Excel.Worksheet sheet, int lastCellRowNum, XlRgbColor color)
{
  FormatCondition cond = sheet.get_Range("A1:I"+lastCellRowNum,Type.Missing).FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlEqual, sheet.Cells[1,1]);
  cond.Interior.Color = color;
}

This code compares the exact value of Cell[1,1] with the others, not just a piece of string contained.

Basically, what I want is a format that allows a "Contains" condition to improve the performance of my code. For example, if in Cell[1,1].Value2 is "Hello", I want any Cell with Value2 equal to "ByeHelloBye" or any other string containing "Hello" to be included in the conditional.

Right now I have to call this method 30 more times as much as I want because I can't figure out how to make this condition. To apply all the formats to 70.000 rows is taking me 35 seconds. Way too much.

Other possible solution to my problem would be:

1. Passing a whole 2D array of colors to Excel.

Sorry about my english, and thanks in advance.

Answer 1

So a friend found the answer to this awful problem. The function that allows this "Contains" format actually exists. This is how it is done:

 FormatCondition cond = sheet.get_Range("A1:I70000", Type.Missing).FormatConditions.Add(XlFormatConditionType.xlTextString, Type.Missing, Type.Missing, Type.Missing, "SomethingToFilterIfContained", XlContainsOperator.xlContains, Type.Missing, Type.Missing);
 cond.Interior.Color = color;

I hope this helps someone.