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

Question

In Excel VBA, I want to create a formula that takes a value from the source cell and format.

I currently 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 cell value. The parts I commented on return a #VALUE error in the cell. When uncommenting, it seems that the color of the link is preserved, but Application.Caller returns error 2023. Does this mean that it does not return the required Range object?

If so, how can I get a range object that refers to the cell used by this function? [obviously to set the color to its original value].

Answer 1

Here is 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 

Answer 2

This is the solution I found on the above question using Tim William magic :

 Function cq(thisCel As Range, srcCel As Range) As Variant thisCel.Parent.Evaluate "colorEq(" & srcCel.Address(False, False) _ & "," & thisCel.Address(False, False) & ")" cq = srcCel.Value End Function Sub colorEq(srcCell, destCell) destCell.Interior.Color = srcCell.Interior.Color End Sub 

destCell is just a reference to the cell into which the function is called.

interior.color can be exchanged or added using other formatting rules. In this decision, three additional points can be distinguished:

• By storing the calculation of the value in the formula, this stops the possibility of a circular reference when destCell refers to itself. If it is placed in a sub , it constantly recounts; and
• If the format changes only when the initial value is changed, and not in the format, as this is the only trigger for starting UDF and, thus, changing the format;
• Application.Caller or Application.ThisCell cannot be integrated, as if it referred to itself and returned a value for itself, it starts an infinite loop or a "circular link". If it is included in the address to create the string, then this works, although in accordance with Tim William's answer.