Change Calling Cell Interior Color

Question

I'm trying to create a very simple VBA function in Excel that calculates a specific value (based on another cells contents) and sets the calling cells value and interior color. I'm fine with the value calculation, but it's the interior coloring that is throwing me for a loop.

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

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

But I'd rather set the cell interior color. I've tried a couple of different iterations of the code below, but this always gives me a value error in the calling cell.

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

Anyway, I've seen some other SO posts that talk about similar changes (E.g. here), but their solutions don't seem to work for me. I would rather not do this with conditional formatting because I want something that I can easily transfer between different Excel files.

Answer 1

With both of these in a 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 a UDF in Excel to update the worksheet