Forum Discussion
Solved
Excel vba: crash on change conditional format
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
- 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
2 Replies
- 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 - Does the sheet in question have many conditional formatting rules? Perhaps they need cleaning up first?
