How to create a VBA formula that takes value and format from source cell

Question

In Excel's VBA I want to create a formula which both takes the value from the source cell and the format.

Currently I have:

Function formEq(cellRefd As Range) As Variant
    'thisBackCol = cellRefd.Interior.Color
    'With Application.Caller
    '    .Interior.Color = thisBackCol
    'End With
    formEq = cellRefd.Value
End Function`

This returns the current value of the cell. The parts that I have commented out return a #VALUE error in the cell. When uncommented it seems the colour of the reference is saved however the Application.Caller returns a 2023 Error. Does this mean that this is not returning the required Range object?

If so how do I get the range object that refers to the cell that the function is used? [obviously in order to set the colour to the source value].

Answer 1

Here's one approach showing how you can still use ThisCell:

Function CopyFormat(rngFrom, rngTo)
    rngTo.Interior.Color = rngFrom.Interior.Color
    rngTo.Font.Color = rngFrom.Font.Color
End Function



Function formEq(cellRefd As Range) As Variant
    cellRefd.Parent.Evaluate "CopyFormat(" & cellRefd.Address() & "," & _
                         Application.ThisCell.Address() & ")"
    formEq = cellRefd.Value
End Function