Making A Function from a Sub

Good Morning,
I have this so called function that works inside my userform, However I have decided to try to get the results in a cell on my worksheet.Can someone help me change the script below to work.

For example in cell B66 I would put something like = mystring1
The problem is I get a name error when I try that method now.

Any Ideas? Thank You, Mike

Please Login or Register  to view this content.

This is the sub from the userform that calls all the functions. I have a sevearl of these functions that refer to different ranges on my worksheet.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

PS. rephrase:

Please Login or Register  to view this content.

into

Please Login or Register  to view this content.

You'll also probably want to make that function volatile, since it deals with colours, and you are not passing a range to it directly.

IIF is the string you have to insert after clicking F1 in the vbeditor.

romperstomper,

Just Curious: Could you explain a little more about "make that function volatile"

Not sure I follow why to or not to use.

And Dont Laugh, how do you make it volatile?

Thank You,

because you re using colours as data (bad idea, IMO) and you are not passing the range as an argument to the function, it will not update when the data or colours change. You make it volatile by adding:

Please Login or Register  to view this content.

to the function code, or by adding a volatile function (like NOW()) to the end of the function call in the cell. Note: changing cell colours does not cause a recalculation so you won't see the formula update after changing colours until you do something that actually causes the workbook to recalculate.