Change cell formatting

Question

How can I change formatting of the cell that uses vba function from the code of that function?

Example I tried:

made vba module (see code below)
put in excel sheet in some cell "=test()"
function "works" - it changes cell value and shows 2 popup windows. But formatting stays the same

Function test()
    MsgBox (Application.ThisCell.NumberFormat)        ' shows "General"
    Application.ThisCell.NumberFormat = "Currency"
    'Application.ThisCell.NumberFormat = "#,##0_);[Red](#,##0)"
    MsgBox (Application.ThisCell.NumberFormat)        ' still shows "General"
    
    test = 12345.6
End Function

How to make it work?

(I need custom formatting rule, not "currency", but custom rule (test example in commented line) doesn't work too)

A User-Defined Function in a cell *can't* change the sheet formatting, except in some edge cases. It's not meant to be used to try changing the format, but just to return the value to the cell. — BigBen, May 05 '20 at 20:26
https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel — Tim Williams, May 05 '20 at 20:31
Seems you're just hard-coding a format not based on anything. Just set the formatting another way. You *can* write something to do that. Write a Sub that you call from a button on the worksheet or...somewhere else. — SmileyFtW, May 05 '20 at 20:58
Workaround - https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280 — Tim Williams, May 06 '20 at 00:50

score 0 · Answer 1 · answered Sep 12 '22 at 12:39

Please don't kill me for this wacko solution :-)

I have created a function, resulting in the number 4096:

Function very_weird_UDF()
  very_weird_UDF = 4096
End Function

Then I have created a conditional formatting rule, based on the present of that UDF (User-Defined Function) inside the formulatext of that cell, as you can see here:

Conditional formatting's formula: =FIND("very_weird_UDF",FORMULATEXT((B2)))

Screenshot of what it looks like:

For your information: Cell "C3" contains the formula, the other cells in B2:C5 just contain the value 4096.
Cells E2:F5 contain the mentioned =Find(...) formula.

Oh, if you wonder why I use such a weird name for the UDF? Just imagine I used a simpler name, like 'a' or 'f' or something like that. That might highlight a lot of cells who don't use that formula (like a simple =If(...) or an =And(...) or ...

Change cell formatting

1 Answers1