Conditional Formatting in Excel with C#
Solution 1
The following code, adds a conditional formatting to the cell range of D1 to E10
It compares the values D1 = E1 or D2 = E2 respectively. you can set the font color, or color fill on 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;
Solution 2
Let's say you want to color cells B1:B10
if their values are not equal to those of A1:A10
, i.e.
B1<>A1
results in B1
being colored, B2<>A2
results in B2
being colored 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 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.
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.
For completeness: I'm using optional objects on Range.Address
property like so Range.Address[isRowAbsolute,isColumnAbsolute]
Solution 3
Try this
FormatCondition cond = ColumnBRange.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlNotEqual, "=$B1");
cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
Comments
-
Cannon almost 2 years
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#
-
Cannon about 12 yearsBut how do I handle dynamic ranges ?
-
Akanksha Gaur about 12 years@buffer_overflow check the link for the declaration of get_range method, you can provide cell 1 n 2 using targetSheet.Cells[1,2] and targetSheet.Cells[3,4] respectively. I hope u gt it
-
Si8 over 7 yearsWhat if it's just one column duplicates only? Check all of
D1
for example... if two duplicates exist, highlight it. -
Akanksha Gaur over 7 yearsYou need to change the condition for duplicates in column =COUNTIF($A$1:$A$10,A1) = 1 where A1 to A10 is your value range.
-
Ross Brasseaux about 2 yearsI've been looking all over for an easy way to pick my colors in VSTO! Thanks!