Change the color of the interior of the calling cell

Question

I am trying to create a very simple VBA function in Excel that calculates a specific value (based on the contents of other cells) and sets the value of the calling cells and the color of the interior. I'm fine with calculating the value, but this is the inner coloration that throws me in a loop.

I can do the following to set the color of text and font:

Function Test() Application.Caller.Font.ColorIndex = 3 Test = "Hello" End Function 

But I would prefer to set the color of the interior of the cell. I tried a couple of different iterations of the code below, but that always gives me a value error in the calling cell.

 Function Test() Application.Caller.Interior.ColorIndex = 3 Test = "Hello" End Function 

Anyway, I saw a few other SO posts that talk about similar changes (like here ), but their solutions don't seem to work for me. I would prefer not to do this with conditional formatting because I want something that I can easily transfer between different Excel files.

Answer 1

With both of them in the regular module:

 Sub ChangeIt(c1 As Range) c1.Interior.ColorIndex = 3 End Sub Function Test() With Application.Caller .Parent.Evaluate "Changeit(" & .Address(False, False) & ")" End With Test = "Hello" End Function 

See: Using UDF in Excel to Update a Worksheet