6

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.

Ryan
  • 1,064
  • 1
  • 7
  • 14

1 Answers1

11

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

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • That does the trick! Thanks @tim. That is more complicated than I would have expected given how easy it is to change font color... At least it works. – Ryan Oct 11 '17 at 20:38
  • 1
    For some reason font color is one thing which works directly from a function: pretty much everything else requires a work-around. – Tim Williams Oct 11 '17 at 21:31