Excel Conditional Formatting Formula Conversion

[H-S]

Excel Automation Experts, please help me!

Does anyone know how to get excel conditional formatting to maintain
the formula actually specified. i.e. in C# overcome the oddity
described best at:
http://j-walk.com/ss/excel/odd/odd07.htm


I C# code simular to the following:
Application xlApp = new Application();
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office
installation and project references are correct.");
return;
}
xlApp.Visible = true;

Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws = (Worksheet)wb.Worksheets[1];
Excel.FormatCondition fc = null;
int row = 10;
int column = 20;
string strFormula = "=D4"
//
//THE important bit!
//
fc = ((Range)ws.Cells[row,
column]).FormatConditions.Add(XlFormatConditionType.xlCellValue,
XlFormatConditionOperator.xlEqual,
strFormula,
null);
//
//

As per the oddity mentioned above the cell reference in the formula is
modified to be relative to the target cell. I am guessing I can
correct this with the "fc.Application.ConvertFormula" method but I can
not work out the correct syntax and MSDN is a bit lacking on the
documentation front!

I need to do this as columns and rows are likely to be moved about and
hence constant references (e.g. "$D$4") are not appropriate, instead I
need to use non-constant references (e.g. "D4") in my formula.

Any help would be much appreciated MSDN and google have failed to
provide an answer!

 

[H-S]

This problem can be avoided by selecting the cell prior to applying
conditional formatting. ;-) Its easy when you know how!