The documentation in https://docs.microsoft.com/en-gb/dotnet/api/microsoft.office.interop.excel.formatconditions says:
The FormatConditions collection can contain up to three conditional formats.
Which is further reinforced in https://docs.microsoft.com/en-gb/dotnet/api/microsoft.office.interop.excel.formatconditions.add?view=excel-pia#Microsoft_Office_Interop_Excel_FormatConditions_Add_Microsoft_Office_Interop_Excel_XlFormatConditionType_System_Object_System_Object_System_Object_System_Object_System_Object_System_Object_System_Object_, which says:
You cannot define more than three conditional formats for a range.
However, I can define more than 3 conditional formats for a range of cells. I did so in a C# application I developed in Microsoft Visual Studio 2008, using a target framework of .NET Framework 3.5, and referencing Microsoft.Office.Interop.Excel.dll version 12.0.0.0.
using System;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace test
{
class Program
{
static int Main( string[] args )
{
//------------------------------
// Parse command line for output path.
string path = null;
if ( args.Length > 0 )
{
path = args[0];
}
Console.WriteLine( "Output path: '" + path + "'" );
//------------------------------
// The Excel application.
Application app = new Application();
// Disable appearance of Excel window.
app.Visible = false;
// Disable screen updates, so that changing cells is faster.
app.ScreenUpdating = false;
// Disable prompts, like whether to overwrite an existing file.
app.DisplayAlerts = false;
//------------------------------
// The workbook.
Workbook book = app.Workbooks.Add( Missing.Value );
//------------------------------
// The worksheet.
Worksheet sheet = (Worksheet)book.ActiveSheet;
//------------------------------
// The first row.
int r = 1;
int c = 1;
sheet.Cells[r, c++] = "R";
sheet.Cells[r, c++] = "Y";
sheet.Cells[r, c++] = "G";
sheet.Cells[r, c++] = "B";
sheet.Cells[r, c++] = "X";
sheet.Cells[r, c++] = "R";
sheet.Cells[r, c++] = "Y";
sheet.Cells[r, c++] = "G";
//------------------------------
// The cells to format.
Range range = sheet.get_Range( sheet.Cells[1, 1], sheet.Cells[1, 8] );
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
//------------------------------
// *** Add more than 3 conditional format rules as a test. ***
//------------------------------
//------------------------------
// Red status.
FormatCondition cond = (FormatCondition)range.FormatConditions.Add(
XlFormatConditionType.xlCellValue, // type
XlFormatConditionOperator.xlEqual, // operator
"R", // formula1: The value or expression associated with the conditional format.
Type.Missing, // formula2
Type.Missing, // object string
Type.Missing, // textOperator
Type.Missing, // dateOperator
Type.Missing // scopeType
);
cond.Font.Bold = true;
cond.Font.Color = XlColor.WHITE;
cond.Interior.Color = XlColor.STATUS_RED;
//------------------------------
// Yellow status.
cond = (FormatCondition)range.FormatConditions.Add(
XlFormatConditionType.xlCellValue,
XlFormatConditionOperator.xlEqual,
"Y",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );
cond.Font.Bold = true;
cond.Interior.Color = XlColor.STATUS_YELLOW;
//------------------------------
// Green status.
cond = (FormatCondition)range.FormatConditions.Add(
XlFormatConditionType.xlCellValue,
XlFormatConditionOperator.xlEqual,
"G",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );
cond.Font.Bold = true;
cond.Font.Color = XlColor.WHITE;
cond.Interior.Color = XlColor.STATUS_GREEN;
//------------------------------
// Blue status.
// *** The 4th condition I supposedly should not have. ***
cond = (FormatCondition)range.FormatConditions.Add(
XlFormatConditionType.xlCellValue,
XlFormatConditionOperator.xlEqual,
"B",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );
cond.Font.Bold = true;
cond.Font.Color = XlColor.WHITE;
cond.Interior.Color = XlColor.STATUS_BLUE;
//------------------------------
// Another status for good measure.
// *** The 5th condition I supposedly should not have. ***
cond = (FormatCondition)range.FormatConditions.Add(
XlFormatConditionType.xlCellValue,
XlFormatConditionOperator.xlEqual,
"X",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );
cond.Font.Bold = true;
cond.Font.Color = XlColor.WHITE;
cond.Interior.Color = XlColor.BLACK;
//------------------------------
// Save workbook to output path.
book.SaveAs( path, // fileName
XlFileFormat.xlWorkbookDefault, // fileFormat
Type.Missing, // password
Type.Missing, // writeResPassword
false, // readOnlyRecommended
false, // createBackup
XlSaveAsAccessMode.xlNoChange, // accessMode
XlSaveConflictResolution.xlLocalSessionChanges, // conflictResolution: Should not matter since the workbook I create is not shared, but probably benign to specify it.
Type.Missing, // addToMru
Type.Missing, // textCodepage
Type.Missing, // textVisualLayout
Type.Missing // local
);
Console.WriteLine( "Saved workbook: '" + book.FullName + "'" );
book.Close( true, // saveChanges: If true, save changes to the workbook. If there is not yet a file name associated with the workbook, then given fileName is used.
path, // fileName: If saveChanges is true and fileName is omitted, the user is asked to supply a file name.
false // routeWorkbook
);
return 0;
}
}
/// <summary>
/// B8G8R8 color values that can be used with Microsoft.Office.Interop.Excel API.
/// </summary>
public enum XlColor
{
BLACK = 0x000000,
WHITE = 0xffffff,
RED = 0x0000ff,
GREEN = 0x00ff00,
BLUE = 0xff0000,
YELLOW = 0x00ffff,
MAGENTA = 0xff00ff,
CYAN = 0xffff00,
STATUS_RED = 0x0707be,
STATUS_YELLOW = 0x00ffff,
STATUS_GREEN = 0x50b000,
STATUS_BLUE = 0x784e1f,
};
}
I open the resulting Excel file using Microsoft Office Professional Plus 2016. All 5 of the conditional formatting rules I defined work.
Am I just lucky? Or is the documentation outdated?