使用C＃在Excel中进行条件格式化

I need to apply a color to a cell's text if the value is not same as a value in another column. What would be the best approach for it ? The way I can think of 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);
                        }
                    }

Tried conditional formatting as 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#

我正在使用VSTO，C＃

3 个解决方案

#1

The following code, adds a conditional formatting to the cell range of D1 to E10

以下代码将条件格式添加到D1到E10的单元格区域

It compares the values D1 = E1 or D2 = E2 respectively. you can set the font color, or color fill on the FormatCondition Object.

它分别比较值D1 = E1或D2 = E2。您可以在FormatCondition对象上设置字体颜色或颜色填充。

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;

#2

Try this

尝试这个

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

#3

Let's say you want to color cells B1:B10 if their values are not equal to those of A1:A10, i.e.

假设您想要为单元格B1：B10着色，如果它们的值不等于A1：A10的值，即

B1<>A1 results in B1 being colored, B2<>A2 results in B2 being colored etc.

B1 <> A1导致B1被着色，B2 <> A2导致B2被着色等。

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 prepending "=" to ColumnARange.Address[false,true] is required because otherwise the Add method uses the Address as a literal string instead of a cell reference.

请注意，必须在ColumnARange.Address [false，true]前加上“=”，否则Add方法将Address用作文字字符串而不是单元格引用。

If you take look at the conditional formatting rule applied to the B1:B10 cells in the Excel sheet it will state Cell Value <> B1 for every cell in the range which is a bit confusing IMO but the formatting is applied correctly nonetheless.

如果您查看应用于Excel工作表中的B1：B10单元格的条件格式设置规则，它将为该范围中的每个单元格指定单元格值<> B1，这有点让IMO感到困惑，但仍然正确应用了格式。

For completeness: I'm using optional objects on Range.Address property like so Range.Address[isRowAbsolute,isColumnAbsolute]

为了完整性：我在Range.Address属性上使用可选对象，如Range.Address [isRowAbsolute，isColumnAbsolute]

#1