Excel vba: crash on change conditional format

[Wannes_Tal]

Hi,

I have a conditional format on a range.

Because the size of the range can change, I want also adapt dynamically the range on which the conditional format speaks .

Since you cannot use named ranges in conditional format, I use VBA:

'range.ModifyAppliesToRange newrange'

This works, but as soon the next line of code is launched, then excel crashes and closes after a few seconds. This next line is: debug.print "test", so this should not fail.

I tried with a pauze after changing the cond format range, but unsuccessful (I thought maybe excel needs some time to change the format).

I also tested this on different computers.

Any suggestions to resolve this?

kr

Wannes

[#2]

Does the sheet in question have many conditional formatting rules? Perhaps they need cleaning up first?

[#3 · Accepted solution]

I have found a solution (for this possible bug?).
I now call a separate procedure, which contains only one line of code: range.ModifyAppliesToRange newrange. No matter how much times I call the procedure, it runs flawless.
For example:

sub example()
dim cfs as formatconditions
dim newFcRange as range
set cfs = myRange.formatconditions
set newFcRange = your new range here
For t = 1 to 8
call changeCF( cfs(t), newFcRange )
next t
do other stuff if needed...
end sub

sub changeCF( cf As FormatCondition, rg As Range )
cf.ModifyAppliesToRange rg
end sub