Prevent the edit of a cell backcolor from call the associated function ?

Excel:  I want to be able to edit the contents of a cell and not have it call the function that the cell contains.
The following function creates an error "Circular reference ..."

// Cell Contents:
  = MyProc(A1 - A2, A3)

// Code from module
function  MyProc(val as long, cell as variant) as string

    if val < 0 then
         MySheet.Cells(cell).Locked = True                                          // <--  this doesn't help
         MySheet.Cells(cell).Interior.Color = vbRed
         MySheet.Cells(cell).Locked = False
    end if

    MyProc = cstr(val)
end function
sidwelleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
You can't use a UDF to change the format, e.g. Interior, of a cell.
sidwelleAuthor Commented:
Yes, but it also updates the cell and fires the function again.
Circular reference ...
NorieAnalyst Assistant Commented:
What exactly are you trying to do with this function?

Where are you using?

If you are using it in a cell on a worksheet you can't use it to do anything other than return a value to the cell it's being used in.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sidwelleAuthor Commented:
Exactly, I want to return a value, but I also want to set some attributes on the cell.

There is no way to prevent the cells from calling the function again ?
some way to disable the auto-update ?
NorieAnalyst Assistant Commented:
You cannot set the attributes, e.g. formatting, of a cell using a UDF.
sidwelleAuthor Commented:
I do it all the time, just is the first time I tried from a function called by the cell.
NorieAnalyst Assistant Commented:
Can you post an example?
sidwelleAuthor Commented:
I did in my initial question:

// Code from module
 function  MyProc(val as long, cell as variant) as string
          MySheet.Cells(cell).Interior.Color = vbRed       // <-- here ...
 end function 

Open in new window

NorieAnalyst Assistant Commented:
How would you use the function in a cell?
sidwelleAuthor Commented:
=MyProc(A1, A1)
NorieAnalyst Assistant Commented:
If you used that in a cell it would return #VALUE! to the cell the formula was in and no formatting would be applied.
sidwelleAuthor Commented:
You ask how to set the cell properties ?

// Code from module
 function  MyProc(val as long, cell as variant) as string
          MySheet.Cells(cell).Interior.Color = vbRed       // <-- here ...
          MyProc = cstr(val)          // <-- fixed 
 end function 

Open in new window

NorieAnalyst Assistant Commented:
Again, if used on a worksheet that formula will result in #VALUE! returned to the cell it's placed in and no formatting applied.
sidwelleAuthor Commented:
Yes, it does this because as soon as you access or edit any of the cells attributes or value,  the code immediately exits the proc.
Microsoft must have left some feature behind disable the call back to the cell to allow you to continue w/u'r procedure ?

That's what I am looking.

Place a break-point on the following line and what the compiler exit the proceedure:
MySheet.Cells(cell).Interior.Color = vbRed
NorieAnalyst Assistant Commented:
I'm sorry but I'm not following.

You cannot use a UDF to apply formatting.

What is it you exactly asking/wanting to do?

PS I know the procedure will exit on that line as it will on any line in a UDF that causes an error.
sidwelleAuthor Commented:
Are you stating that you can update the format from a function ?
Run the function from a button, it works fine !
NorieAnalyst Assistant Commented:
No I'm stating that you cannot use a function in a cell to apply formatting.
sidwelleAuthor Commented:
Yes, because it calls for an update to the cell.
Is there a way to prevent the cell from making this call ?
NorieAnalyst Assistant Commented:
Remove the code that tries to do the update?
sidwelleAuthor Commented:
That's the code that I want to keep !
NorieAnalyst Assistant Commented:
I'm sorry but you can't keep that code.

As I've being saying all through this you cannot use a UDF to apply formatting.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sidwelleAuthor Commented:
I don't have time to pursue this anymore, but I am disappointed the M$ didn't leave you a method to update the format w/o invoking a cell-update.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.