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>

 

[GlennUK]

Can't see your formulas ... surround them with code tags.

 

[Tigs81]

sorry i didnt realize.

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(SUM(G1:G10*H1:H10)>3,TRUE,FALSE)

<NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"< font>
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

<?XML:NAMESPACE PREFIX = NOW(),IF(E4 /><NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"< font>Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(SUM(G1:G10*H1:H10)>3,TRUE,FALSE)"</NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"<>

</NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"<><NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"< font><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>I have also noticed that when entering the array formula via VBA it does actually evaluate the first row eg G1*H1>3 but does do the other rows until the conditional format window is opened and ok is clicked to close it.

<NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"< font>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)<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>
</NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"<><NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"< font>

<o>Any Idea's?</o>
<o></o>
<o>Thanks</o>
<o>tigs</o> </NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"<></NOW(),IF(E4:E6="""",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)"<>

 

[GlennUK]

First guess, run your VBA on a single cell, not a range. Does it work?

 

[Tigs81]

Thank you Glenn

Is that what you meant?

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(SUM(G1*H1,G2*H2,G3*H3)>3,TRUE,FALSE)"

I tried this vba code and it works fine.

Thankyou
tigs

 

[GlennUK]

No, I actually meant:

Range("G11").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(SUM(G1:G10*H1:H10)>3,TRUE,FALSE)"

 

[Tigs81]

no that didnt work.
same result only calculates on the first row.

 

[GlennUK]

How about replacing the SUM with SUMPRODUCT?

 

[Tigs81]

Glenn
sumproduct does work.

Although my actual formula that i need to work is a bit more than that. I was using that as an example as it shows the issue with the array formula.

=IF(OR(IF(F4:F10<NOW(),IF(F4:F10="",FALSE,TRUE),FALSE))=TRUE,TRUE,FALSE)

Its comparing dates in cell range and any are before Now conditional formatting is activated.

I have also just thought of a workaround by utilising another column which gets around the array formula issue with the conditional formatting
But would like to keep the worksheet tidy and not use an extra column

Any further help you can offer on this matter, would be greatly appreciated.

thanks
tigs

 

[GlennUK]

Can you spaces either side of your " < " and " > " signs when you next post your formula ... I should be able see it then. I can't guess whether SUMPRODUCT is appropriate or not at the moment.