VBA cannot correctly set a conditional formating rule of Type:=2 with an array formula

Dear all,

I came across an issue when creating in VBA a Format Condition (FC) of Type 2 (xlExpression) with an array formula.

When entering the formula manually in the Excel FC panel (without Shift + Control + Enter), it is automatically recognized as an array formula (no brackets added by the GUI).

I am sure of this because the FC format can only apply to the cell(s) when the Formula1 returns "True". And it returns "True" only when interpreted as an array formula (verified as a cell formula).

Defining this exact same formula (FC.Formula1) in VBA does not make it an array formula !

Here is an Excel file example -> https://cjoint.com/c/JHzsH2QNV64

Note that I suspect a reason, but need your help to confirm (using the example file).

In VBA the FC.Formula1 is valued in local format (here Office French language).

Maybe this would work in native English Office ?

Thanks in advance for help.

Best Regards,

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Replies (0)

Question Info

Last updated October 5, 2021 Views 5 Applies to:

VBA cannot correctly set a conditional formating rule of Type:=2 with an array formula

Replies (0) 

Question Info

Replies (0)