Strange Conditional Formatting behaviour...

[Matty]

Hi,

I am using some VBA to apply Conditional Formatting to a Worksheet. The application of the rule works just fine.

However, while the Conditional Formatting formula is evaluating correctly, for whatever reason, the formatting isn't applied unless I manually go into the Conditional Formatting Rules Manager and hit 'Apply'.

Is there something I need to include in my code to ensure all formatting is refreshed and "applied"? Or is this a known bug?

I am using Excel 2007.

Thanks,

Matty

 

[VoG]

Have you tried adding

Application.ScreenUpdating=True

 

[Matty]

Hi Peter,

Have you tried adding

Application.ScreenUpdating=True

Yes. This sits right at the bottom of my code.

It's all quite odd. I've never had this problem in the past when I've done this kind of thing.

Any other ideas?

Thanks,

Matty

 

[DJAXE]

Is Automatic calculation turned off?

If so, try turning it on or hitting F9.

 

[Matty]

Hi All,

I have come across this same behaviour again today.

The issue seems to occur when one is using an array lookup formula (to another Worksheet) to denote whether the Conditional Formatting should be applied or not.

Let me provide an example to illustrate what I mean...

Here's my source data on Sheet1:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:81px;"><col style="width:111px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Fruit</td><td>Type</td><td>Place</td><td>Preferred Supplier</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Apple</td><td>Bag</td><td>Shop</td><td>Yes</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Pear</td><td>Bunch</td><td>Supermarket</td><td>No</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Orange</td><td>Loose</td><td>Market</td><td>Yes</td></tr></tbody></table>
And here's my summary data on Sheet2:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:83px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Fruit</td><td>Type</td><td>Place</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Apple</td><td>Bag</td><td style="background-color:#c2d69a; ">Shop</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Pear</td><td>Bunch</td><td>Supermarket</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Orange</td><td>Loose</td><td style="background-color:#c2d69a; ">Market</td></tr></tbody></table>
I have used the following formula in Conditional Formatting:

=INDEX(PS,MATCH(1,IF(FRUIT=$A2,IF(TYPE=$B2,IF(PLACE=$C2,1))),0))="Yes"

Which applies a green format if the Preferred Supplier in Sheet1 for the Fruit/Type/Place equals "Yes".

Now, when I apply this, it works as expected. However, if I save the file and then re-open it, the formatting is no longer applied. And the only way I can get Excel to re-apply the formatting is to go back into the Edit Formatting Rule Window (note that no change is made to the formula), clicking OK, and then hitting Apply in the Conditional Formatting Rules Manager Window.

Is there something I can use to force/trigger the re-application of this Conditional Formatting? Or is my only option to employ some code to re-write it each and every time the Worksheet is opened, which seems like overkill?

Cheers,

Matty

 

[Marcelo Branco]

Now, when I apply this, it works as expected. However, if I save the file and then re-open it, the formatting is no longer applied. And the only way I can get Excel to re-apply the formatting is to go back into the Edit Formatting Rule Window (note that no change is made to the formula), clicking OK, and then hitting Apply in the Conditional Formatting Rules Manager Window.

Matty,

I faced exactly this problem some time ago.

As a workaround i saved the file as .xls (Excel 2003) or .xlsb (bynary) and everything worked fine.

Try saving as .xls or .xlsb and see if this works.

M.

 

[Matty]

Hi Marcelo,

I'm glad to hear someone else has noticed this issue and that it isn't just me!

I'll give your suggestion a go to see if it solves the problem.

Thanks for responding.

Matty

 

[Marcelo Branco]

Matty,

See this (october 2010)

http://www.mrexcel.com/forum/showthread.php?t=498987&page=2

M.

 

[Matty]

Hi Marcelo,

Thanks for the link.

Did you ever get a response from MS?

I'm going to play about with alternative formulas to the array one I've used to see if it fixes the issue. And I'll also try your suggestion of saving the file in a different format to see if that works.

It's reassuring to know that I haven't just missed something simple here...

Matty

 

[barry houdini]

I think Marcelo has solved it for you Matty but just to confirm - yes this is an issue with excel 2007 only I believe, and happens with any array formula (or actually a formula that needs to be confirmed with CTRL+SHIFT+ENTER). It has been discussed before amongst the MrExcel MVPs....

You could use this version of your formula to avoid CSE

=INDEX(PS,MATCH(1,INDEX((FRUIT=$A2)*(TYPE=$B2)*(PLACE=$C2),0),0))="Yes"