VBA Conditional Format. Formula Array

[Tigs81]

Hi

I have an issue with the conditional format when i manually enter the an array formula into the formula field in the conditional format window it works.
eg =IF(OR(IF(E4:E6<NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"

If i enter the same array formula via VBA it doesnt work until i manual open the conditional format window and click ok to close it.
eg Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(OR(IF(E4:E6<NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"

if i do the same thing with this non array formula it works fine both via vba and manually.
=IF(SUM(G4:G6)>3,TRUE,FALSE)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o>I have read somewhere that conditional formatting always treats the formulas as a array formula but it doesnt seem to do that when i have added it in via VBA.</o>
<o></o>
<o>Any Idea's?</o>
<o></o>
<o>Thanks</o>
<o>tigs</o>

 

[Tigs81]

=if(or(if(f4:f10 < now(),if(f4:f10 = "",false,true),false)) = true,true,false)

 

[GlennUK]

How about:

=SUMPRODUCT(--(F4:F10 < NOW())*--(F4:F10 <> "")) > 0

as the condition formula?

 

[Tigs81]

that works perfectly. Thank you very much.

I have used sumproduct before but not like that. I didnt realise that it was so powerful.

thanks
tigs.

 

[GlennUK]

I'm glad that worked.

Yes, SUMPRODUCT is very powerful. I end up using it quite a lot.