Problem with conditional formatting based on user-defined function

[Kelvin Stott]

Does anyone have a better workaround to this problem, rather than just deleting the conditional formatting as microsoft suggests:

Odd refresh behavior after you specify a conditional format in Excel

 

[Andrew Poulsom]

Can't you just remove the MsgBox statement from your UDF?

 

[Kelvin Stott]

In this case I don't have any message box in my UDF, but get the same problem as the limits of conditional formatting are defined by my UDF (see cause no. 2).

 

[Andrew Poulsom]

What's your UDF?

 

[Kelvin Stott]

ADVERTISEMENT

Just a calculation to return a single number, depending on a fairly complex select case series (too complex to enter directly into formatting formula box).

 

[Andrew Poulsom]

That's not helpful. Post it so that I can try to reproduce your problem.

 

[Kelvin Stott]

Sorry, I'm connected to the internet by phone but not by computer, so I can't post the function. However I get the same problem even when the UDF is just one line:

MyUDF = 1

 

[Kelvin Stott]

OK, I figured out that I can store the value of the UDF in a cell, and then refer to this cell in the conditional formatting. Not ideal, but that seems to bypass the screen refresh issue.