CODEWITHSUNDEEP
c#excelformattingexcel-2007excel-interop
Jian_H
Jian_H

Reputation: 173

C# conditional formatting excel

In my project I have code that formats the cell's back color based on a cell value condition like this:

//if value is 1 than colorIndex is 3;
Excel.FormatCondition condition = colorRange.FormatConditions.Add(
    Type: Excel.XlFormatConditionType.xlTextString, 
    Operator: Excel.XlFormatConditionOperator.xlEqual,
    Formula1: "=1");
condition5.Interior.ColorIndex = 3;

This condition works fine, but the condition in the following code does not work: 

//if value is Red Color than colorIndex is 3;
Excel.FormatCondition condition = colorRange.FormatConditions.Add(
    Type: Excel.XlFormatConditionType.xlTextString, 
    Operator: Excel.XlFormatConditionOperator.xlEqual,
    Formula1: "=Red Color");
condition5.Interior.ColorIndex = 3;

The error messgage I receive is "Incorrect Parameter"

What am I doing wrong?

Upvotes: 2

Views: 4561

Answers (2)

Dheeraj Singh
Dheeraj Singh

Reputation: 5183

You can try this out. This code provides me solution

 Excel.FormatCondition condition = colorRange.FormatConditions.Add(
     XlFormatConditionType.xlCellValue, 
     XlFormatConditionOperator.xlEqual,
     "=\"Red Color\"", 
     Type.Missing, 
     Type.Missing, 
     Type.Missing, 
     Type.Missing, 
     Type.Missing);

Upvotes: 1

Francesco Baruchelli
Francesco Baruchelli

Reputation: 7468

I think the errors comes from the fact that the Formula1 parameter you are passing is a formula. This means that it should be written like the formulas that you manually write in the Excel cells. This means that when you write "Red color" as your parameter, Excel tries to convert it to a Formula, but the syntax is wrong and it gives you the error.

You can try to put just "=Red" (without the color part of the string), and it wouldn't give you an error, but it wouldn't work anyway, because it would compare the value in your cell with the one contained in the cell named Red (and I suppose you haven't such a cell in your worksheet).

The solution is to put " around you string in this way:

Excel.FormatCondition condition = colorRange.FormatConditions.Add(
    Type: Excel.XlFormatConditionType.xlTextString, 
    Operator: Excel.XlFormatConditionOperator.xlEqual,
    Formula1: "=\"Red Color\"");

Upvotes: 1

Related Questions