ConditionalFormats to affect another cell

[Elsa]

Hi!

Can a conditional format affect a different cell that may or may not be in the same worksheet as the cell with the conditional format? If so, how is this accomplished?

Thanks!

Elsa

[Elsa]

Now I understand conditional formatting a bit better. The cell that has the conditional formatting will be affected by the logical value of one or more cells.

I am trying to use your ConditionalFormats property and refer to named ranges like this:

//Applying conditional formatting to "A7" for format type as CellValue( LessOrEqual)
sheet.Range["A7"].Text = "Show";
IConditionalFormats condition6 = sheet.Range[ "A7" ].ConditionalFormats;
//Adding formats to IConditionalFormats collection
IConditionalFormat condition7 = condition6.AddCondition();
condition7.FormatType = ExcelCFType.Formula;
condition7.FirstFormula = "=OR([Value1] = 0, [Value2] > 0, [Value3] = 13)";
sheet.Range["A7"].Text = "Either Value1 = 0 or Value2 > 0 or Value3 = 13";
//Setting format properties.
condition7.Color = ExcelKnownColors.White;

I am getting an error "Can''t parse formula." It doesn''t seem to understand "Value1" or "[Value1]." How can I fix this?

Thanks,

Elsa

>Hi!
>
>Can a conditional format affect a different cell that may or may not be in the same worksheet as the cell with the conditional format? If so, how is this accomplished?
>
>Thanks!
>
>Elsa

[Administrator]

Hi Elsa,

I was able to see some limitations in our conditional format formula support but I was able to work around these issues, here is the modified sample code that works

workbook.ThrowOnUnknownNames = false;

lname = workbook.Names.Add("Value1");
lname.RefersToRange = sheet.Range["A1"];

lname = workbook.Names.Add("Value2");
lname.RefersToRange = sheet.Range["A3"];

lname = workbook.Names.Add("Value3");
lname.RefersToRange = sheet.Range["A5"];

IConditionalFormats formats = sheet.Range["A7"].ConditionalFormats;
//Adding formats to IConditionalFormats collection
IConditionalFormat condition7 = formats.AddCondition();
condition7.FormatType = ExcelCFType.Formula;
condition7.FirstFormula = "=OR(Value1=0,Value2>0,Value3=13)";
//Setting format properties.
condition7.BackColor = ExcelKnownColors.Red;
sheet.Range["A7"].Text = "Either Value1 = 0 or Value2 > 0 or Value3 = 13";

Notes on the modified code

1) Please add the names to the workbook and not to the worksheet.

2) There should be no empty space in a formula

Please let me know if you have any questions.

Thanks,
Stephen.

[Elsa]

This did it. Thank you!