Conditional formatting in Excel using C #

Question

I need to apply color to the text of the cell if the value does not match the value in another column. What would be the best approach for this? The way I can think is quite expensive.

for (int i = 0; i < ColumnARange.Cells.Count; i++) { if (ColumnARange.Cells[i, 1] != ColumnBRange.Cells[i, 1]) { Range currCell = ColumnBRange.Cells[i, 1]; currCell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } } 

I tried conditional formatting as shown below, but in vain.

 FormatCondition cond = ColumnBRange.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlNotEqual, ColumnARange); cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); 

I am using VSTO, C #

Answer 1

The following code adds conditional formatting to a cell range from D1 to E10

It compares the values ​​of D1 = E1 or D2 = E2, respectively. You can set the font color or color fill in the FormatCondition object.

 FormatCondition format =(FormatCondition)( targetSheet.get_Range("D1:E10", Type.Missing).FormatConditions.Add(XlFormatConditionType.xlExpression, XlFormatConditionOperator.xlEqual, "=$D1=$E1", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)); format.Font.Bold = true; format.Font.Color = 0x000000FF; 

Answer 2

try it

 FormatCondition cond = ColumnBRange.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlNotEqual, "=$B1"); cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); 

Answer 3

Suppose you want to color cells B1:B10 if their values ​​are not equal to the values A1:A10 , i.e.

B1<>A1 leads to the color B1 , B2<>A2 leads to the color B2 , etc.

Then you can do the following

 Range columnBRange = (Range)oSheet.Range[oSheet.Cells[1,2], oSheet.Cells[10,2]]; Range columnARange = (Range)oSheet.Range[oSheet.Cells[1,1], oSheet.Cells[1,1]]; FormatCondition cond = (FormatCondition) ColumnBRange.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlNotEqual, "="+ColumnARange.Address[false,true]); cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); //Red letters cond.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow); //Light yellow cell background 

Note that you need to add from "=" to ColumnARange.Address[false,true] , because otherwise the Add method uses Address as a literal string instead of a cell reference.

If you look at the conditional formatting rule that applies to cells B1:B10 in an Excel worksheet, it will indicate Cell Value <> B1 for each cell in the range that confuses IMO a bit, but formatting is applied nevertheless.

For completeness: I use optional objects in the Range.Address property, for example, Range.Address[isRowAbsolute,isColumnAbsolute]